SQL Server : split row values into columns, multiple sets

Question

I have searched the questions, not finding an exact answer to mye specific question.

I have a table with these values:

Case    Milestone   Sorting Date
1       A           1       A1  
1       B           2       B2  
1       A           3       A3  
1       B           4       B4  
2       A           1       A1  
3       A           1       A1  
3       B           2       B2

Basically I would like to go through the rows and move the date to the corresponding column; Milestone A goes to column A with value = date. The problem is that I have multiple sets of cases with identical milestones. Sorting should be used to determine which milestones belong to which case.

The end result should be this:

Case    A   B       
1       A1  B2 
1       A3  B4
2       A1  
3       A1  B2

Any ideas how to accomplish this?

Thank you very much,

Kjell

You know how many times this question has been answered in SO. Search for `PIVOT` or `Cross tab` — Pரதீப், Apr 30 '15 at 09:22
possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Maciej Los, Apr 30 '15 at 09:30

Deepshikha · Answer 1 · 2015-04-30T09:52:33.050

Write as:

select * from 
( select [Case],Milestone,[Date],
         row_number() over (partition by [Case],Milestone 
         order by Sorting) as rownum
  from Test )
  as T
  pivot 
  ( max([Date]) 
    for Milestone in ([A],[B])
   ) as PVT

DEMO

[Update] You can write dynamic pivot as :

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Milestone)
FROM (SELECT DISTINCT Milestone FROM Test) AS T
--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery =     
'select [Case], ' + @ColumnName + ' from 
( select [Case],Milestone,[Date],
         row_number() over (partition by [Case],Milestone 
         order by Sorting) as rownum
  from Test )
  as T
  pivot 
  ( max([Date]) 
    for Milestone in (' + @ColumnName + ')
   ) as PVT
   order by [Case] asc' 

 --Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

DEMO

SQL Server : split row values into columns, multiple sets

1 Answers1