Combine and transpose rows data into columns

Question

I have a table on a SQL database that looks like this

Person  | Color
Alex    | red
Alex    | blue
Alex    | orange
Mike    | green
Tom     | blue
Tom     | black

and I need to make a copy like this

Person | Color_1| Color_2| Color_3
Alex   | red    | blue   | orange
Mike   | green
Tom    | blue  | black

This is not a restriction (I use MS SQL Server). It's an one time operation but involves hundreds of thousands of records. I could use any other DBMS. — Andrei Lupusoru, Feb 09 '18 at 08:19
Possible duplicate of [SQL Server: Examples of PIVOTing String data](https://stackoverflow.com/questions/24470/sql-server-examples-of-pivoting-string-data) — Mazhar, Feb 09 '18 at 09:00

score 1 · Accepted Answer · answered Feb 09 '18 at 09:09

We need to do this dynamically. We need to get the total number of columns we are going to have. We need to create a dynamic T-SQL for our PIVOT.

Try this:

CREATE TABLE #DataSource
(
    [name] VARCHAR(12)
   ,[color] VARCHAR(12)
);

INSERT INTO #DataSource ([name], [color])
VALUES ('Alex', 'red')
      ,('Alex', 'blue')
      ,('Alex', 'orange')
      ,('Mike', 'green')
      ,('Tom ', 'blue')
      ,('Tom ', 'black');

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);

DECLARE @MaxNumberOfColorsPerName INT;

WITH DataSource AS
(
    SELECT COUNT([color]) OVER (PARTITION BY [name]) AS [ColorsCount]
    FROM #DataSource
)
SELECT @MaxNumberOfColorsPerName = MAX([ColorsCount])
FROM DataSource;

SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                    SELECT TOP (@MaxNumberOfColorsPerName) ',Color_' +  CAST(ROW_NUMBER() OVER(ORDER BY t1.number) AS VARCHAR(12)) AS N
                                    FROM master..spt_values t1 
                                    CROSS JOIN master..spt_values t2                             
                                    ORDER BY ROW_NUMBER() OVER (ORDER BY t1.number)
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynammicTSQLStatement = N'
SELECT *
FROM 
(
    SELECT *
          ,''Color_'' + CAST(ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY [color]) AS VARCHAR(12)) AS [color_number]
    FROM #DataSource
) DS
PIVOT
(
    MAX([color]) FOR [color_number] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;

Sreenu131 · Answer 2 · 2018-02-09T15:01:08.413

First i manipulate the data into required format

IF OBJECT_ID('tempdb..##Getdata')IS NOT NULL
DROP TABLE ##Getdata

;With cte(Person, Color)
AS
(
SELECT 'Alex'    , 'red'    UNION ALL
SELECT 'Alex'    , 'blue'   UNION ALL
SELECT 'Alex'    , 'orange' UNION ALL
SELECT 'Mike'    , 'green'  UNION ALL
SELECT 'Tom'     , 'blue'   UNION ALL
SELECT 'Tom'     , 'black' 
)
,Cte_Final
AS
(
SELECT DENSE_RANK()OVER(ORDER BY Person )AS Rnk
      ,Person
      ,Color 
      ,'Color_'+CAST(DENSE_RANK()OVER(ORDER BY Person ) AS VARCHAR(2)) AS ColrCol
 FROM cte
)
SELECT DISTINCT Rnk
        ,Person
        ,ColrCol
        ,STUFF((SELECT DISTINCT  ', '+Color 
                    FROM Cte_Final i WHERE i.Rnk=o.Rnk
                 FOR XML PATH ('')),1,1,'') AS
                  Color
    INTO ##Getdata
FROM Cte_Final o

Using Dynamic Sql i get the expected result

DECLARE @COlumn nvarchar(max),@Sql nvarchar(max)

SELECT  @COlumn=STUFF((SELECT DISTINCT  ', '+'Split.a.value(''/S['+CAST(Rnk AS VARCHAR(2))+']'''+','+'''nvarchar(100)'''+') As '  + QUOTENAME(ColrCol )
                    FROM ##Getdata i
                 FOR XML PATH ('')),1,1,'') 

SET @Sql='SELECT DISTINCT Person,'+@COlumn+' FROM
            (
            SELECT Person, 
            CAST(''<S>''+REPLACE (Color,'','',''</S><S>'')+''</S>'' AS XML ) AS Color
            FROM ##Getdata
            ) AS A
            CROSS APPLY Color.nodes(''S'') AS Split(a)
            '
PRINT @Sql
EXEC(@Sql)

Result

Person  Color_1 Color_2  Color_3
--------------------------------
Alex     blue    orange  red
Mike     green   NULL    NULL
Tom      black   blue    NULL

view demo from below link Expected Result for your data

Combine and transpose rows data into columns

2 Answers2