将动态SQL Server Pivot(UNPIVOT)列名转换为行值 [英] Dynamic SQL Server Pivot ( UNPIVOT ) column name to a row value

问题描述

我正在使用SQL Server.我有一个查询，该查询返回1行数据.

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

SELECT *
FROM DBO.MY_TABLE
WHERE ID = 5

它看起来像这样:

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.

诀窍在于，我并不总是确切地知道会有几列，可能会有2或20列.可能会有所不同.

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.

但是只有一行数据.

推荐答案

所以您有几个问题...第一个问题是这需要动态sql，因为表和列不会提前知道，所以您不能只需使用简单的枢轴即可.

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.

您的第二个问题是您所有的数据类型都是未知的，因此您必须将所有列都转换为可以支持所有长度的任何内容... varchar(max).

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 */

最后，我不能出于良心推荐使用动态sql的解决方案，而不会警告这种动态sql涉及的危险(从性能角度和注入的可能性).如果您想增加对这一主题的了解，请阅读这篇出色的文章.

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)列名转换为行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！