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) 列名到行值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:动态 SQL Server Pivot (UNPIVOT) 列名到行值


基础教程推荐
- 如何根据该 XML 中的值更新 SQL 中的 XML 2021-01-01
- 在 MySQL 中:如何将表名作为存储过程和/或函数参数传递? 2021-01-01
- MySQL 中的类型:BigInt(20) 与 Int(20) 2021-01-01
- 二进制文件到 SQL 数据库 Apache Camel 2021-01-01
- 什么是 orradiag_<user>文件夹? 2022-01-01
- 在多列上分布任意行 2021-01-01
- mysql选择动态行值作为列名,另一列作为值 2021-01-01
- 表 './mysql/proc' 被标记为崩溃,应该修复 2022-01-01
- 如何在 SQL 中将 Float 转换为 Varchar 2021-01-01
- oracle区分大小写的原因? 2021-01-01