Convert row data to column in SQL Server

Question

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

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

The output is:

ENTITYID    NAME    SPOUSE  CHILD
1           DONNA   HENRY   JACK
2           NULL    NULL    KAYALA

But he wanted the output something like:

ENTITYID    NAME    SPOUSE  CHILD   CHILD
1           DONNA   HENRY   JACK    KAYALA

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.

Is this possible?

How do you know that Kayala belongs to Donna and Henry? Or is the sample wrong in that Kayala would have an Entity ID of 1 also? — Hart CO, Jun 12 '13 at 22:05
Actually there is a supporting table that has entityid and parententityid relationship. But, I guess to simplify this, I can have the same entity id. — Sri Reddy, Jun 13 '13 at 16:00

Hart CO · Accepted Answer · 2013-06-13T16:30:51.153

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

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.

Here is a Demo: SQL Fiddle

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

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

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)

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.

This works if we know how many childs. But what happens if we don't know the limit :) — Sri Reddy, Jun 13 '13 at 16:02
:-) Thanks, this works. I should have been clear with my question. — Sri Reddy, Jun 13 '13 at 17:05

score 0 · Answer 2 · edited Aug 04 '14 at 07:09

if you don't need to show entity id look at your question correctly there is you want output:-

ENTITYID    NAME    SPOUSE  CHILD   CHILD
1           DONNA   HENRY   JACK    KAYALA(This one is entityId=2)

so is incorrect then even you want result like this:

  NAME    SPOUSE  CHILD   CHILD
  DONNA   HENRY   JACK    KAYALA

SELECT * FROM  (SELECT  PROPERTYNAME, PROPERTYVALUE FROM STAGING) AS T PIVOT (MAX(PROPERTYVALUE) FOR PROPERTYNAME IN (NAME, SPOUSE, CHILD)) AS T2

Convert row data to column in SQL Server

2 Answers2

Linked

Related