SQL - Transpose one column of data into a row

Question

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!

Answer 1

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:

ClientGUID	DxCode1	DxCode2	DxCode3	DxCode4	DxCode5	DxCode6	DxCode7	DxCode8	DxCode9	DxCode10
12345	50.8	62.5	42.1	null	null	null	null	null	null	null

• Example db<>fiddle

Answer 2

In this type of query the number of values may vary and it is generally simpler and more flexible to use STRING_AGG() to list all the values.
We can choose the seperator used between the values. One option which can be interesting is to use a newline and present the values as a vertical list.

SELECT
  ClientGUID,
  STRING_AGG(DxCode, ', ') DxCodes
FROM myTable
GROUP BY ClientGUID
ORDER BY ClientGUID;

ClientGUID | DxCodes            
---------: | :------------------
     12345 | 50.80, 62.50, 42.10

db<>fiddle here