今天,我的同事要求我将数据从垂直临时表转换为水平表.我的意思是将行转换为列.我用了PIVOT并解决了它.但是如果数据字段重复,我遇到了移动数据的麻烦.
以下是我正在处理的测试数据：
CREATE TABLE STAGING
(
ENTITYID INT,PROPERTYNAME VARCHAR(25),PROPERTYVALUE VARCHAR(25)
)
INSERT INTO STAGING VALUES (1,'NAME','DONNA')
INSERT INTO STAGING VALUES (1,'SPOUSE','HENRY')
INSERT INTO STAGING VALUES (1,'CHILD','JACK')
INSERT INTO STAGING VALUES (2,'KAYALA')
我使用PIVOT将行数据显示为列：
SELECT * FROM (SELECT ENTITYID,PROPERTYNAME,PROPERTYVALUE FROM STAGING) AS T PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME,SPOUSE,CHILD)) AS T2
输出是：
ENTITYID NAME SPOUSE CHILD 1 DONNA HENRY JACK 2 NULL NULL KAYALA
但他希望输出类似于：
ENTITYID NAME SPOUSE CHILD CHILD 1 DONNA HENRY JACK KAYALA
底线是,登台表中可以有多个CHILD属性.我们需要考虑这一点,并将所有儿童移至列.
这可能吗？
解决方法
您可以在属性名称中添加行号,以便您可以执行所需操作：
SELECT * FROM ( SELECT ENTITYID,PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID,PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5)),PROPERTYVALUE FROM #STAGING ) AS T PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME1,SPOUSE1,CHILD1,CHILD2,CHILD3,CHILD4,CHILD5)) AS T2
我假设ENTITYID将子项绑定到父项,即同一个人的所有子项的ENTITYID为1,但您的示例显示Kayala为2.
这是一个演示：SQL Fiddle
如果你只想要CHILD字段的数字,你可以这样：
PROPERTYNAME = CASE WHEN PROPERTYNAME LIKE '%CHILD%' THEN PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID,PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5)) ELSE PROPERTYNAME END
然后从IN()语句中的其他字段中删除该数字.
奖金问题 – 动态执行以上操作：
我们不想假设人们只有一个配偶或2.3个孩子,所以我们动态地做一点：
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT ',' + PROPERTYNAME
FROM (SELECT DISTINCT PROPERTYNAME = PROPERTYNAME + CAST(ROW_NUMBER() OVER(PARTITION BY ENTITYID,PROPERTYNAME ORDER BY PROPERTYVALUE) AS VARCHAR(5))
FROM STAGING )sub
ORDER BY CASE WHEN PROPERTYNAME LIKE '%NAME%' THEN 1
WHEN PROPERTYNAME LIKE '%SPOUSE%' THEN 2
WHEN PROPERTYNAME LIKE '%CHILD%' THEN 3
ELSE 4
END,RIGHT(PROPERTYNAME,1)
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,'')
SET @query = 'SELECT * FROM
(
SELECT ENTITYID,PROPERTYVALUE
FROM STAGING
) AS T
PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN ('+@cols+')) AS T2
'
EXEC(@query)
注意：订购仅适用于配偶1-9和子女1-9,您可以调整以适应,但无论如何它是任意的.
