-1

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

sseager
  • 21
  • 4
  • I haven't tried anything as wasn't sure where to start or if it was possible :-( – sseager May 30 '17 at 12:53
  • [Transpose rows to columns based on ID column](https://stackoverflow.com/q/19714304/1048425), [Efficiently convert rows to columns in sql server](https://stackoverflow.com/q/15745042/1048425), [Simple way to transpose columns and rows in Sql?](https://stackoverflow.com/q/13372276/1048425), [How to convert rows into columns in SQL Server?](https://stackoverflow.com/q/42396816/1048425), [SQL Server : Transpose rows to columns](https://stackoverflow.com/q/20111418/1048425), [How to transpose rows into columns in SQL Server?](https://stackoverflow.com/q/43259726/1048425) – GarethD May 30 '17 at 12:59
  • Possible duplicate of [Pivot Dynamic Columns, no Aggregation](https://stackoverflow.com/questions/11985796/pivot-dynamic-columns-no-aggregation) – Tab Alleman May 30 '17 at 13:45

3 Answers3

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
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
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

YuGagarin
  • 341
  • 7
  • 20
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

Ullas
  • 11,450
  • 4
  • 33
  • 50