Transpose SQL columns to rows

Question

I have the table below and would like to transpose this in SQL, I have tried using sql PIVOT but I am getting nowhere. Any help is much appreciated.

CID ActID   ActType         ActDate
1   10      Assessment      2017-08-09
1   11      StartOfPOC      2017-11-01
1   22      POC1            2017-11-03
1   22      POC2            2017-11-03
2   44      Report          2017-11-03
2   44      Planning        2017-11-03
3   66      Assessment      2017-11-06
3   66      POC1            2017-11-06
3   77      EndOfPOC        2017-11-06

I would like to transpose this table to the below

CID     ActType1    ActDate1    ActType2    ActDate2    ActType3    ActDate3    ActType4    ActDate4    ActType4    ActDate4        
1       Assessment  2017-08-09  StartOfPOC  2017-11-01  POC1        2017-11-03  POC2        2017-11-03
2       POC1        2017-11-03  Planning    2017-11-03
3       Assessment  2017-11-06  POC1        2017-11-06  EndOfPOC    2017-11-06

Below is what i have but want to improve from here.

SELECT * FROM (
  Select
     CID
    ,ActID   
    ,ActType         
    ,ActDate
  from #tbl r
  where r.ActType in ('Assessment','Start of POC','POC1','POC2','Report','Planning','EndOfPOC')  
) x
PIVOT( MAX(ActDate)
FOR ActType IN (
     [Assessment]
    ,[Start of POC]
    ,[POC1]
    ,[POC2]
    ,[Reporting]
    ,[Planning]
    ,[End of POC]
    )
) p

Thanks

If you don't show us your SQL, however can we help you fix it? — Thom A
– Thom A ♦, Commented Mar 13, 2019 at 15:36
I find the syntax for PIVOT to be exceptionally obtuse. I prefer to use cross tabs, aka conditional aggregation. Here is a great article on the topic. sqlservercentral.com/articles/T-SQL/63681 and if you need a dynamic version, sqlservercentral.com/articles/Crosstab/65048 — Sean Lange
– Sean Lange, Commented Mar 13, 2019 at 15:38

John Cappelletti · Accepted Answer · 2019-03-13 15:50:44Z

6

Rather than PIVOT, I would use a Conditional Aggregation in concert with Row_Number()

Example

Select CID
      ,AcctType1 = max(case when RN = 1 then ActType end)
      ,AcctDate1 = max(case when RN = 1 then ActDate end)
      ,AcctType2 = max(case when RN = 2 then ActType end)
      ,AcctDate2 = max(case when RN = 2 then ActDate end)
      ,AcctType3 = max(case when RN = 3 then ActType end)
      ,AcctDate3 = max(case when RN = 3 then ActDate end)
      ,AcctType4 = max(case when RN = 4 then ActType end)
      ,AcctDate4 = max(case when RN = 4 then ActDate end)
From (
        Select * 
              ,RN= Row_Number() over (Partition By CID Order by ActDate)
         From YourTable
     ) A
 Group By CID

Returns

answered Mar 13, 2019 at 15:50

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jk1844 Over a year ago

John, thanks for this this is excellent, this is exactly what I wanted. Much appreciated.

John Cappelletti Over a year ago

@jk1844 Always happy to help

Collectives™ on Stack Overflow

Transpose SQL columns to rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related