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
