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:

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

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!

CodePudding user response：

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:

• Example db<>fiddle

CodePudding user response：

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