Converting row to column in Sql Server 2005

Consider the following data and our target is to have all the ClientId starting form 247 to 252 will be in column
name Client1, Client2... etc. 

UserID      CaseID      CaseNumber  ClientID
----------- ----------- ----------- -----------
80          216         1087        247
80          216         1087        248
80          216         1087        249
80          216         1087        250
80          216         1087        251
80          216         1087        252
80          276         1140        328
80          277         1143        329
80          347         1191        438
80          348         1192        439

SQL Server 2005 introduced Ranking, Partitioning and Pivoting. By using all togather
we can achive our goal.



Ranking functions that provide the ability to rank a record within a partition. 
In this case, we can use RANK() to assign a unique number for each record, and partition
by the ClientID (so that the RANK will reset for each ClientID)

By prefixing some text to the rank number, we end up with something like:

SELECT UserID, C.CaseID, CaseNumber, ClientID, 
'ClientId' + CAST(
    RANK() OVER (
    PARTITION BY C.CaseID, CaseNumber
 ORDER BY ClientID) AS VARCHAR(10)) ClientIdListing
FROM  [Case] C, CaseClient CC
WHERE C.CaseId = CC.CaseID AND USerID = 80

Result:

UserID      CaseID      CaseNumber  ClientID    ClientIdListing
----------- ----------- ----------- ----------- ------------------
80          216         1087        247         ClientId1
80          216         1087        248         ClientId2
80          216         1087        249         ClientId3
80          216         1087        250         ClientId4
80          216         1087        251         ClientId5
80          216         1087        252         ClientId6
80          276         1140        328         ClientId1
80          277         1143        329         ClientId1
80          347         1191        438         ClientId1
80          348         1192        439         ClientId1
            

The new column (ClientIdListing) is the concatenation of the literal string "ClientId"
and the string representation of the number that the RANK function returned. But
the bigger point is that now this column can be used for pivoting, and result in
a series of new columns called [ClientId1], [ClientId2], [ClientId3], etc.




Pivoting in SQL Server 2005 requires explicit declaration of values as a column
list. In this case, we can't just say "Pivot on the ClientIdListing column", but
rather must say "Pivot on the ClientIdListing column, and make new columns only
for these specific values". This restriction is a little bit of a downside because
we need knowledge of the values in the column. Or, in this case, we need to know
how many ClientIds a Case could possibly have so that we create enough columns in
the result.




So here is the final query:

SELECT * FROM 
    (SELECT  UserID, C.CaseID, CaseNumber, ClientID, 'ClientId' 
        + CAST(RANK() OVER (PARTITION BY C.CaseID, CaseNumber
        ORDER BY ClientID) 
AS VARCHAR(10)) ClientIdListing
    FROM [Case] C, CaseClient CC
    WHERE C.CaseId = CC.CaseID 
    AND USerID = 80) P 

    PIVOT 
    (MAX(ClientID) FOR ClientIdListing IN 
        (ClientID1, ClientID2, ClientID3, ClientID4, ClientID5, ClientID6)
    ) AS Clients 

And here is the Output:

UserID      CaseID      CaseNumber  ClientID1   ClientID2   ClientID3   ClientID4   ClientID5   ClientID6
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
80          216         1087        247         248         249         250         251         252
80          276         1140        328         NULL        NULL        NULL        NULL        NULL
80          277         1143        329         NULL        NULL        NULL        NULL        NULL
80          347         1191        438         NULL        NULL        NULL        NULL        NULL
80          348         1192        439         NULL        NULL        NULL        NULL        NULL