将行数据转换为SQL Server中的列 [英] Convert row data to column in SQL Server

问题描述

今天，我的同事要求我将数据从垂直登台表转换为水平表.我的意思是将行转换为列.我使用了PIVOT并解决了它.但是遇到了这样的情况:如果数据字段重复出现，我将无法移动数据.

Today I was asked by my colleague to transform data from a vertical staging table into a horizontal table. I mean transform rows to column. I used PIVOT and resolved it. But got into situation where I am getting trouble to move data if the data field repeats itself.

这是我正在处理的测试数据:

Here is the test data that I am working on:

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, 'CHILD', 'KAYALA')

我使用PIVOT将行数据显示为列:

I used PIVOT to show row data as columns:

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属性.我们需要考虑这一点，并将所有儿童移动到列中.

bottom line is that there can be more than one CHILD attribute coming into the staging table. And we need to consider this and move all the CHILDREN to columns.

这可能吗?

推荐答案

您可以在属性名称中添加一个行号，该行号将允许您执行所需的操作:

You can add a row number to the propertyname that will allow you to do what you want:

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.

I'm assuming here that the ENTITYID ties the children to the parent, ie all children for the same person have ENTITYID of 1, but your example shows a 2 for Kayala.

这里是一个演示: SQL Fiddle

如果您只想要CHILD字段的数字，则可以输入以下内容:

If you only want the numbers for the CHILD fields you could put this:

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()语句中的其他字段中删除该数字.

Then remove the number from the other fields in your IN() statement.

奖金问题-动态执行上述操作: 我们不想假设人们只有一个配偶或2.3个孩子，因此我们会动态地进行全部操作:

Bonus Question- Do the above dynamically: We don't want to assume that people only have one spouse or 2.3 children, so we do the whole bit dynamically:

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,1,'')


SET @query = '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 ('+@cols+')) AS T2

'
EXEC(@query)

注意:此顺序仅适用于1-9岁的配偶和1-9岁的孩子，您可以对其进行调整以适合自己，但这仍然是任意的.

Note: The ordering will only work for spouses 1-9 and children 1-9, you can adjust that to suit, but it's arbitrary anyway.

这篇关于将行数据转换为SQL Server中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！