CODEWITHSUNDEEP
sql-server
PMK
PMK

Reputation: 297

Sql Server Pivot with multiple columns from a record

I am using Sql Server 2014. I currently have a table which looks something like this:

labNumber   parameter   result  date
18897       Nitrate     <0      1/20/2007
18897       Coliform    NEG     1/21/2007
18897       Arsenic     1       1/22/2007

I need to create a view where there is only one record which would look like this:

labNumber   nitate  nitrateDate  coliform   coliformDate    arsenic   arsenicDate
18897       <0      1/20/2007    NEG        1/21/2007       1         1/22/2007

Before I realized that each records with the same lab number had a different date I hada view using PIVOT which looked like this:

  SELECT *
  FROM  (SELECT  labNumber, result, parameter 
         FROM ResultsTable) src 
         PIVOT (MAX(result) 
         FOR parameter IN ([Nitrate], [Coliform], [Arsenic])) p

I am completely stumped how to accomplish the results I want.

Upvotes: 2

Views: 44

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Considering you have just record per parameter, Here is one way using conditional aggregate 

SELECT labNumber,
       Max(CASE WHEN parameter = 'Nitrate' THEN result END) AS nitate,
       Max(CASE WHEN parameter = 'Nitrate' THEN date END) nitateDate,
       Max(CASE WHEN parameter = 'Coliform' THEN result END) Coliform,
       Max(CASE WHEN parameter = 'Coliform' THEN date END) ColiformDate,
       Max(CASE WHEN parameter = 'Arsenic' THEN result END) Arsenic,
       Max(CASE WHEN parameter = 'Arsenic' THEN date END) ArsenicDate
FROM   yourtable
GROUP  BY labNumber

Another approach by unpivoting and pivoting the data 

SELECT labNumber,
       Nitrate,
       NitrateDate,
       Coliform,
       ColiformDate,
       Arsenic,
       ArsenicDate
FROM  (SELECT labNumber,
              intr,
              col
       FROM   Yourtable
              CROSS apply (VALUES (result,parameter),
                                  (convert(varchar(20),[date],120),parameter + 'Date')) cs(intr, col))a
      PIVOT (Max(intr)
            FOR col IN (Nitrate,
                        NitrateDate,
                        Coliform,
                        ColiformDate,
                        Arsenic,
                        ArsenicDate))pv

Upvotes: 2

Related Questions