IF OBJECT_ID('temp..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
GO
CREATE TABLE #temp
(
[CxID] int,
[CompanyID] int,
[Category] int -- this category column is created by ROW_NUMBER function over partition by [CxID] order by [CxID] and [CompanyID]
)
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('1', '101', '1');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('1', '102', '2');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('1', '103', '3');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('2', '201', '1');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('3', '301', '1');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('4', '401', '1');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('5', '501', '1');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('5', '502', '2');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('5', '503', '3');
INSERT INTO #temp ([CxID], [CompanyID], [Number]) VALUES ('5', '504', '4');
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = (SELECT STRING_AGG(category,',') FROM (SELECT DISTINCT category FROM #temp WHERE category IS NOT NULL)t);
set @query = 'SELECT date, ' + @cols + ' from
(
select CxID
, CompanyID
, category
from #temp
) x
pivot
(
max(CompanyID)
for category in (' + @cols + ')
) p ';
execute(@query);
Error Message: Parse error at line: 11, column: 34: Incorrect syntax near '1'.
Question: We don't know the maximum number of CompanyID that customer can have in the historical data, and want to output like following:
| CxID | CompanyID1 | CompanyID2 | CompanyID3 | ... | CompanyIDx |
|---|---|---|---|---|---|
| 1 | 101 | 102 | 103 | ... | (NULL) |
| 2 | 201 | (NULL) | (NULL) | ... | (NULL) |
| 3 | 301 | (NULL) | (NULL) | ... | (NULL) |
| 4 | 401 | (NULL) | (NULL) | ... | (NULL) |
| 5 | 501 | 502 | 503 | ... | ... |