问题描述
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) 列名到行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！