Pivot values on column based on grouped columns in SQL

Question

I want to pivot values to columns based on a group. However, I do not know the values beforehand.

A query gives me this result.

Id          Code         EntityId
----------- ------------ -------------
3           22209776     1
4           143687971    3
4           143687971    4
4           143687971    5
4           143687971    15
5           143658155    7
5           143658155    8

I would like to output this

Id          Code         EntityId1     EntityId2     EntityId3     EntityId4
----------- ------------ ------------- ------------- ------------- -------------
3           22209776     1             NULL          NULL          NULL
4           143687971    3             4             5             15
5           143658155    7             8             NULL          NULL

Answer 1

You could try using the pivot function:

declare @tmp TABLE (id int, Code int, EntityId NVARCHAR(10))

insert into @tmp (id, Code, EntityId)
values  (3, 22209776 , 1),
        (4, 143687971, 3),
        (4, 143687971, 4),
        (4, 143687971, 5),
        (4, 143687971, 15),
        (5, 143658155, 7),
        (5, 143658155, 8)

select 
     pvt.id
    ,pvt.Code
    ,[1] as EntityID1
    ,[2] as EntityID2
    ,[3] as EntityID3
    ,[4] as EntityID4
from (  
    select 
         id, Code, EntityId
        ,ROW_NUMBER() over(partition by code order by EntityId) as RowNum
from 
    @tmp
) a
pivot (MAX(EntityId) for RowNum in ([1], [2], [3], [4])) as pvt

Answer 2

If you do now know how many column you are going to have in the result, you need to use dynamic T-SQL statement to build the PIVOT. For example:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN;
    DROP TABLE #DataSource;
END;

CREATE TABLE #DataSource
(
    [id] INT
   ,[Code] INT
   ,[EntityId] INT
);

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@Columns NVARCHAR(MAX);

DECLARE @MaxColumns INT;

INSERT INTO #DataSource ([id], [Code], [EntityId])
VALUES (3, 22209776 , 1)
      ,(4, 143687971, 3)
      ,(4, 143687971, 4)
      ,(4, 143687971, 5)
      ,(4, 143687971, 15)
      ,(5, 143658155, 7)
      ,(5, 143658155, 8)
      ,(4, 143687971, 25)
      ,(4, 143687971, 26);

-- we need to know how many columns are going to be shown
SELECT TOP 1 @MaxColumns = COUNT(*)
FROM #DataSource
GROUP BY [Code]
ORDER BY COUNT(*) DESC;

-- we are building here the following string '[1],[2],[3],[4],[5],[6]'; 
-- this will change depending the input data
WITH gen AS 
(
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 
    FROM gen 
    WHERE num+1<=@MaxColumns
)
SELECT @Columns = STUFF
(
    (
        SELECT ',[EntityId' + CAST([num] AS VARCHAR(12)) + ']'
        FROM gen
        FOR XML PATH(''), TYPE

    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,1
    ,''
)
OPTION (maxrecursion 10000);

SET @DynamicTSQLStatement = N'
SELECT *
FROM 
( 
    SELECT [id]
          ,[Code]
          ,[EntityId]
          ,''EntityId'' + CAST(ROW_NUMBER() OVER(PARTITION BY [Code] ORDER BY [EntityId]) AS VARCHAR(12))
    FROM #DataSource
) DS ([id], [Code], [EntityId], [RowID])
PIVOT 
(
    MAX([EntityId]) for [RowID] in (' + @Columns +')
) PVT;';

EXEC sp_executesql @DynamicTSQLStatement;