Converting Row to Column dynamically in SQL

Question

I have table like that below in my Microsoft SQL server. I want to convert the rows into column.

My issue is I don't know the result ahead and there can be more than 3 rows in the result.

Current table

ID - Function - Result - MasterKey
1  - 100      - 31     - 7
1  - 125      - 34     - 7
1  - 138      - 39     - 7
.  - .        - .      - .
.  - .        - .      - .

Desired Result

MasterKey - Function1 - Result1 - Function2 - Result2 - Function3 - Result3 - ...
7         - 100       - 31      - 125       - 34      - 138       - 39      - ...

How can I achieve it ? Thanks in advance.

No, it's different. For his case, it partially know the result ahead. — kevin, Mar 10 '14 at 11:53
If, however, you look at the answer, it makes no presumption of knowledge of the output rows. Generally speaking wanting to do this within the RDBMS is considered a code-smell or anti-pattern, but it can be done and the way to do it is to use `FOR XML`, as in the answer I linked to. — MatBailie, Mar 10 '14 at 12:04

score 1 · Accepted Answer · answered Mar 10 '14 at 12:09

You need to create the SQL Dynamically if you have an unknown number of functions/results:

DECLARE @SQL NVARCHAR(MAX) = '';

SET @SQL = 'SELECT MasterKey' + 
            (   SELECT  ', Function' + RowNum + ' = MAX(CASE WHEN RowNumber = ' + RowNum + ' THEN [Function] END)' + 
                        ', Result' + RowNum + ' = MAX(CASE WHEN RowNumber = ' + RowNum + ' THEN Result END)'
                FROM    (   SELECT  DISTINCT 
                                    RowNum = CAST(ROW_NUMBER() OVER (PARTITION BY MasterKey 
                                                                        ORDER BY [Function]) 
                                                    AS VARCHAR(10))
                            FROM    T
                        ) t
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') + 
            ' FROM (SELECT MasterKey, [Function], Result, RowNumber = ROW_NUMBER() OVER (PARTITION BY MasterKey ORDER BY [Function]) FROM T) AS T GROUP BY MasterKey';

EXECUTE sp_executesql @sql;

Example on SQL Fiddle

Converting Row to Column dynamically in SQL

1 Answers1