Pivot an uneven number of column values into grouped rows

Question

I have a table of this structure:

Fred    Jim   John    
11......21....31    
12......22....null    
13......null...null

that I want to pivot to this structure:

Name    Value1  Value2  Value3    
Fred.....11.........12.........13    
Jim.......21........ 22.........null    
John.....13.........null........null

(without the dots, of course).

Before I do this, I can determine the maximum number of values a person has, so I can 'name' the columns beforehand, but I need to be able to fit all the data in - padding with blanks or nulls is OK.

I have looked at other answers, but couldn't find an example for an uneven number of values for each row to be created.

Answer 1

The below code uses OUTER APPLY and ranking functions to achieve the result.

DECLARE @persons TABLE(Fred int,Jim int,John int)

INSERT INTO @persons
VALUES(11,21,31),(12,22,null),(13,null,null)

SELECT  t.Name
        , Max(CASE WHEN t.rnk = 1 THEN t.value END) AS value1
        , Max(CASE WHEN t.rnk = 2 THEN t.value END) AS value2
        , Max(CASE WHEN t.rnk = 3 THEN t.value END) AS value3
FROM (
    SELECT Fred, Jim, John, Row_Number() OVER ( ORDER BY ( SELECT NULL)) AS rnk
    FROM @persons
    ) AS p
OUTER APPLY (
    VALUES ('Fred', Fred, rnk), ('Jim', Jim, rnk), ('John', John, rnk)
    ) AS t(Name, Value, rnk)
GROUP BY Name

Result Set

+------+--------+--------+--------+
| Name | value1 | value2 | value3 |
+------+--------+--------+--------+
| Fred |     11 | 12     | 13     |
| Jim  |     21 | 22     | NULL   |
| John |     31 | NULL   | NULL   |
+------+--------+--------+--------+