Dynamic SQL Server Pivot (UNPIVOT) column name for row value

Question

I am using SQL Server. I have a query that returns 1 row of data.

SELECT * FROM DBO.MY_TABLE WHERE ID = 5 

It will look something like this:

 ID F_NAME L_NAME NUMBER 5 JOE SCHMOE 1234567890 

I need a query / procedure that expands them. I need a result so that it looks like this:

 ID 5 F_NAME JOE L_NAME SCHMOE NUMBER 1234567890 

Basically, the column name becomes the value in the first column, and the row value becomes the value of the second column.

The trick is that I do not always know exactly how many columns there will be, maybe 2 or 20 columns. It can change.

However, there will be only one row of data.

Answer 1

So, you have a couple of problems ... firstly, this requires dynamic sql, because the table and columns are not known in advance, so you cannot just use simple univot.

It also means that you will need to get the column names from the system tables.

The second problem is that all of your data types are unknown, so you need to drop all columns to something that can support anything and any length ... varchar (max).

Thus, given these two obstacles, this solution:

 declare @yourTable varchar(50) declare @yourKeyField varchar(50) declare @yourKey varchar(50) set @yourTable = 'MyTable' /** change to tablename or pass as parameter */ set @yourKeyField = 'ID' /** change to fieldname or pass as parameter */ set @yourKey = '5' /** change to key value or pass as parameter */ declare @query nvarchar(max) select @query = COALESCE(@query+' union all ','') + 'select ''' + c.name + ''' as [Column], Cast([' + c.name + '] AS VarChar(MAX)) as [Value] from ' + @yourTable + ' where ' + @yourKeyField + ' = ''' + @yourKey + '''' from syscolumns c inner join sysobjects o on c.id = o.id and o.xtype = 'u' where o.name = @yourTable order by c.colid exec sp_executesql @query /** execute query */ 

Finally, I cannot, with a clear conscience, recommend a solution that uses dynamic sql without warning about the dangers associated with these (both in terms of performance and injection capabilities). Read this wonderful article if you want to increase your knowledge of this subject.

http://www.sommarskog.se/dynamic_sql.html