Query Help!

Transact-SQL
1

Hello SQL Expert,

I am working on a small test project and wondering if you could help me to update my query below.

SELECT Scientist_ID, Experiment_#1, Experiment#_2
FROM Scientist_TBL

Here is the output from above SELECT statement:

and here is the output that I am trying to achieve:

result set.JPG1210×104 26.8 KB

How do I update my above simple query?

Anyone could help me?

Thanks all for all your help

2

something like this (untested, see below):

select Scientist_ID
        , COUNT(CASE WHEN Experiment_#1 in ('P',' F') THEN 1) AS [Total Experiment #1]
        , COUNT(CASE WHEN Experiment_#1 = 'P' THEN 1 END) AS [Experiment #1 Passed]
        , COUNT(CASE WHEN Experiment_#1 = 'F' THEN 1 END) AS [Experiment #1 Failed]
        , COUNT(CASE WHEN Experiment_#2 in ('P',' F') THEN 1) AS [Total Experiment #2]
        , COUNT(CASE WHEN Experiment_#2 = 'P' THEN 1 END) AS [Experiment #2 Passed]
        , COUNT(CASE WHEN Experiment_#2 = 'F' THEN 1 END) AS [Experiment #2 Failed]
from Scientist_TBL
group by Scientiest_ID

This is untested since you did not post:

  1. CREATE TABLE statement for your table
  2. INSERT INTO statement(s) to populate your table
3

Thank you Gbritton