SQL Server Transpose Rows into Columns using IDs as Column Names

Question

I have a large file that has the following fields:

Table 1:

+---------+--------+-----------+
| User_Id | Key_Id | Value     | 
+---------+--------+-----------+
| 100     | 74     | 37        |
| 100     | 65     | Male      |
| 100     | 279    | G235467   |
+---------+--------+-----------+

and I have another file that tells what each 'Key_Id' is called (they are column names) e.g.

Table 2:

+--------+------------------+
| Key_Id | Key              |
+--------+------------------+
| 65     | Gender           |
| 66     | Height           |
| 74     | Age              |
| 279    | ReferenceNo      |

I want to create a table using the Key_Id names found in the Key column of table 2, transpose all of the values from table 1 into table 2, but also include the User_Id from table 1 as this relates to an individual.

PS. Table 2 has nearly 300 keys that would need turning into individual fields

So ultimately I would like a table that looks like this:

+---------+---------+--------+-------+--------------+--------+
| User_Id | Gender  | Height | Age   | ReferenceNo  |  etc   |
+---------+---------+--------+-------+--------------+--------+
| 100     | Male    |        | 37    | G235467      |        |

So that each User_Id is a row and that all the Keys are columns with their respective values

I haven't tried anything as wasn't sure where to start or if it was possible :-( — sseager
– sseager, Commented May 30, 2017 at 12:53
Transpose rows to columns based on ID column, Efficiently convert rows to columns in sql server, Simple way to transpose columns and rows in Sql?, How to convert rows into columns in SQL Server?, SQL Server : Transpose rows to columns, How to transpose rows into columns in SQL Server? — GarethD
– GarethD, Commented May 30, 2017 at 12:59

Ullas · Accepted Answer · 2017-05-30 12:54:08Z

0

You can use a dynamic sql query as below.

Query

declare @sql as varchar(max);

select @sql = 'select t1.[User_Id], ' + stuff((select +
    ', max(case t2.[Key_Id] when ' + cast([Key_Id] as varchar(100)) + 
    ' then t1.[Value] end) as [' + [Key] + '] '
    from Table2 
    for xml path('')
), 1, 2, '') + 
'from Table1 t1 left join Table2 t2 on t1.[Key_Id] = t2.[Key_Id] group by t1.[User_Id];'

exec(@sql);

Find a demo here

answered May 30, 2017 at 12:54

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sseager Over a year ago

How can I insert these results into a new table?

YuGagarin · Accepted Answer · 2017-05-30 12:53:02Z

0

You need to get a coma-separated list of those 300 key names to be used in PIVOT/UNPIVOT operators in T-SQL like described here

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

answered May 30, 2017 at 12:53

YuGagarin

3511 gold badge7 silver badges20 bronze badges

Comments

Kannan Kandasamy · Accepted Answer · 2017-05-30 12:57:18Z

0

you can use pivot as below:

Select * from (
    Select u.UserId, k.[key], u.[Value] from table1 u
       join table2 k on u.keyid = k.keyid   ) a
pivot ( max([Value]) for [key] in ([Gender], [Height], [Age], [ReferenceNo]) ) p

For dynamic list of keys you can use dynamic sql as below:

Declare @cols1 varchar(max)
Declare @query nvarchar(max)

Select @cols1 = stuff((select ','+QuoteName([Key]) from table2 group by [Key] for xml path('')),1,1,'')

Set @Query = 'Select * from (
    Select u.UserId, k.[key], u.[Value] from table1 u
       join table2 k on u.keyid = k.keyid   ) a 
pivot ( max([Value]) for [key] in (' + @cols1 + ') ) p '

Select @Query  --Check the generated query and execute by uncommenting below query
--exec sp_executesql @Query

edited May 30, 2017 at 12:57

answered May 30, 2017 at 12:47

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

2 Comments

sseager Over a year ago

If I have 300 columns, would I have to write all 300 of them in the pivot line?

Kannan Kandasamy Over a year ago

Updated answer accordingly

Collectives™ on Stack Overflow

SQL Server Transpose Rows into Columns using IDs as Column Names

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related