大佬教程收集整理的这篇文章主要介绍了将行数据转换为SQL Server中的列,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
以下是我正在处理的测试数据:
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()语句中的其他字段中删除该数字.
奖金问题 – 动态执行以上操作:@H_403_39@我们不想假设人们只有一个配偶或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,您可以调整以适应,但无论如何它是任意的.
以上是大佬教程为你收集整理的将行数据转换为SQL Server中的列全部内容,希望文章能够帮你解决将行数据转换为SQL Server中的列所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。