动态 SQL Server 将 ( UNPIVOT ) 列名转换为行值

发布于 2024-12-27 12:21:26 字数 451 浏览 0 评论 0原文

我正在使用 SQL Server。我有一个返回 1 行数据的查询。

SELECT *
FROM DBO.MY_TABLE
WHERE ID = 5

它看起来像这样：

ID    F_NAME    L_NAME    NUMBER
5     JOE       SCHMOE    1234567890

我需要一个查询/过程来旋转它们。我需要结果如下所示：

ID        5
F_NAME    JOE
L_NAME    SCHMOE
NUMBER    1234567890

基本上，列名称成为第一列中的值，而行的值成为第二列的值。

诀窍在于，我并不总是确切知道会有多少列，可能有 2 或 20 列。它可能会有所不同。

但是，只有一行数据。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

原来分手还会想你 2025-01-03 12:21:26

所以你有几个问题......第一是这需要动态sql，因为表和列提前不知道，所以你不能只使用简单的unpivot。

这也意味着您必须从系统表中获取列名。

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

因此，考虑到这两个障碍，这里有一个解决方案：

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 的解决方案，而不警告其中涉及的危险（从性能角度和注入的可能性）。如果您想增加有关该主题的知识，请阅读这篇精彩的文章。

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

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

回复收藏 0 原文

~没有更多了~