Transpose 2 columns multiple rows to 1 rows multiple columns

Question

I am trying to transpose the following table (example) to 1 row where column name is one of the row column 1 and the value of the row will be corresponding columns 2row[x].

Table

|--------------------------------|
|   ID  |     Key     |   Value  |
|--------------------------------|
|   1   |  'Color'    |    '13'  |
|   1   |  'Access'   |    'H'   |
|   1   |  'LastCalc' |  '17.06' |
|   2   |  'Choice'   |  'done'  |
|   2   |   'timeA'   |  '15:43' |
|--------------------------------|

Now i will need to select per ID

Selecting ID 1 will give me

|------------------------|
|     Key     |   Value  |
|------------------------|
|  'Color'    |    '13'  |
|  'Access'   |    'H'   |
|  'LastCalc' |  '17.06' |   
|------------------------|

But i need

|--------------------------------|
| Color |   Access  |  LastCalc  |
|--------------------------------|
|  '13' |    'H'    |   '17.06'  |
|--------------------------------|

the amount of columns to be created with that method will not surpass 300 columns as i have another columns to ensure that the query will query by subset of 300 and create multiple rows for the same ID.

Anyone ever done that ? I have search around but everything i found was for fix amount of columns and did not do what i am looking for. Maybe i am not using proper search keywords.

no, the question you are referring to is a cross table as the column become rows and rows get calculated to become fix column with row value. — Franck, Feb 20 '14 at 15:24

M.Ali · Accepted Answer · 2014-02-20T15:39:28.847

Test Data

CREATE TABLE Test_Table_P(ID INT,[Key] VARCHAR(100),Value  VARCHAR(100))
INSERT INTO Test_Table_P VALUES
(1,'Color','13'),(1,'Access','H'),   (1,'LastCalc','17.06'),
(2,'Choice','done'),(2,'timeA','15:43')

Query

Say you have a variable @ID for which you want all the columns.

DECLARE @ID INT = 1;

to get the columns for a given ID since it will be different for each given id you will need to do the following to the the column name in the format of [col1],[col2],[col3],..... which is required for Pivot syntax.

DECLARE @Cols NVARCHAR(MAX);

SELECT @Cols =  STUFF((SELECT ', ' + [Key] [text()]
                     FROM Test_Table_P
                     WHERE ID = t.ID    
                     FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
                      ,1, 2, '')
FROM Test_Table_P t
WHERE ID = @ID
GROUP BY t.ID

Once you have Columns stored in a @Cols Variable, now you can create the PIVOT query and pass the column names inside the query. As follows...

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT * FROM 
            (
            SELECT ID, [Key], Value
            FROM Test_Table_P
            WHERE ID = @ID)Q
              PIVOT (MAX(Value)
                     FOR [Key]
                     IN (' + @Cols + N')
                    )p'

Once you have build the query you will need to execute it using system stored procedure sp_executesql. and since sp_executesql has its own scope and any variable declared are not visiable to sp_executesql you will need to pass the @ID to sp_executesql as its variable.

EXECUTE sp_executesql @sql
                      ,N'@ID INT'
                      ,@ID

Result Set

╔════╦═══════╦════════╦══════════╗
║ ID ║ Color ║ Access ║ LastCalc ║
╠════╬═══════╬════════╬══════════╣
║  1 ║    13 ║ H      ║ 17.06    ║
╚════╩═══════╩════════╩══════════╝

OMG dynamic pivot, i haven't thought of that one. it works perfectly. luckily it's not millions record i need to query otherwise that would be slow. Small correction for my version of SQL i needed to change `DECLARE @ID INT = 1;` to `DECLARE @ID INT` and `SET @ID = 1` and also dropped the semicolons. — Franck, Feb 20 '14 at 15:44

Transpose 2 columns multiple rows to 1 rows multiple columns

1 Answers1

Linked