将行数据转换为SQL Server中的列

本日,我的同事要求我将数据从垂直姑且表转换为程度表.我的意思是将行转换为列.我用了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,您可以调解以顺应,但无论怎样它是恣意的.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!