动态 SQL Server Pivot (UNPIVOT) 列名到行值

Dynamic SQL Server Pivot ( UNPIVOT ) column name to a row value(动态 SQL Server Pivot (UNPIVOT) 列名到行值)

问题描述

I'm 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 pivots them. I need the result to look like this:

ID        5
F_NAME    JOE
L_NAME    SCHMOE
NUMBER    1234567890

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

The trick is that I do not always know for sure how many columns there will be, there could be 2 or 20 columns. It can vary.

However there will only be ONE row of data.

So you have a couple problems... the first is that this requires dynamic sql because the table and columns are not known ahead of time so you can't just use a simple unpivot.

That also means that you'll have to get the column names from system tables.

Your second problem is that all your datatypes are unknown so you have to cast all the columns to something that can support everything and any length... varchar(max).

So, with those two obstacles in mind here is a 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 can't in good conscience recommend a solution that uses dynamic sql without warning of the dangers involved in such (from both a performance standpoint and the potential for injection). Read this excellent article if you want to increase your knowledge on the subject.

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

这篇关于动态 SQL Server Pivot (UNPIVOT) 列名到行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！