SQL – Transpose one column of data into a row

April 5, 2022

I am looking to take the values of a single column and transpose them into the corresponding row for each ClientGUID. A simple example of the of what I have post SELECT statement is:

ClientGUID	DxCode
12345	50.8
12345	62.5
12345	42.1

What I am trying to accomplish is this, if possible:

ClientGUID	DxCode1	DxCode2	DxCode3
12345	50.8	62.5	42.1

For this example my SELECT statement looks a such:

    SELECT ClientGUID, DxCode
    FROM MyTable
    WHERE ClientGUID = 12345

Any thoughts or direction would be greatly appreciated! Thanks!

>Solution :

You can use PIVOT for this, e.g.:

;WITH src AS
(
  SELECT ClientGUID, DxCode,
    rn = ROW_NUMBER() OVER (PARTITION BY ClientGUID ORDER BY @@SPID)
  FROM dbo.ClientDxCodes
  -- WHERE ClientGUID = 12345
)
SELECT ClientGUID, DxCode1 = [1], DxCode2  = [2], DxCode3 = [3],
    DxCode4 = [4], DxCode5 = [5], DxCode6  = [6], DxCode7 = [7],
    DxCode8 = [8], DxCode9 = [9], DxCode10 = [10]
FROM src
PIVOT 
(
  MAX(DxCode) 
  FOR rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS p;

Output: