需要在 SQL Server 中透视字符串值 [英] Need to Pivot String values in SQL server

问题描述

我将表格描述为:

Occupation String |
Name String

有值:

Developer | A
Developer | B
Designer  | X
Coder     | Y
Coder     | Z

我需要数据透视格式的值:

I need values in pivot format as:

Designer | Developer | Coder
---------+-----------+--------   
   X     |     A     |   Y
   Null  |     B     |   Z

有人可以帮忙吗?

提前致谢

推荐答案

带有 ROW_NUMBER() 的基本 PIVOT 将为您做事:

The basic PIVOT with ROW_NUMBER() will do things for you:

SELECT  [Developer],
        [Designer],
        [Coder]
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
    FROM #temp
) as t
PIVOT (
    MAX(Name) FOR Occupation IN ([Developer],[Designer],[Coder])
) as pvt

输出:

Developer   Designer    Coder
A           X           Y
B           NULL        Z

如果Occupation的数量可能非常多，那么你需要动态SQL:

If the number of Occupations may very then you need dynamic SQL:

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = (
    SELECT DISTINCT ','+QUOTENAME(Occupation)
    FROM #temp
    FOR XML PATH('')
)

SELECT @sql = N'
SELECT  '+STUFF(@columns,1,1,'')+'
FROM (
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
    FROM #temp
) as t
PIVOT (
    MAX(Name) FOR Occupation IN ('+STUFF(@columns,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

注意:我使用 ORDER BY (SELECT NULL) 只是为了获得一些随机排序.为此，最好使用一些实际字段.

Note: I have used ORDER BY (SELECT NULL) just to get some random ordering. Better use some actual field for this purpose.

这篇关于需要在 SQL Server 中透视字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！