How to pivot a table this way when there are 3000 columns-CodePudding

I am using SQL Server

I have a table TT that looks like this

TargetID    RowID   Actual
0001        1         0
0001        2         1
0001        3         1
0002        1         0
0002        2         1
0002        3         0
0003        1         1
0003        2         1
0003        3         0

How can I pivot it is to this

RowID     Target0001     Target0002     Target0003
1            0              0               1
2            1              1               1
3            1              0               0

I tried

SELECT 'TargetID'   TargetID, RowID, Actual
FROM TT 
WHERE TargetID = '0001'
UNION ALL
SELECT 'TargetID'   TargetID, RowID, Actual
FROM TT 
WHERE TargetID = '0002'
SELECT 'TargetID'   TargetID, RowID, Actual
FROM TT 
WHERE TargetID = '0003'

But there are 3000 TargetIDs and my method is not good for that

Any idea how to do that?

CodePudding user response：

You can try this...

DECLARE @ColumnsTable TABLE ([ColumnName] VARCHAR(50));

INSERT INTO @ColumnsTable ([ColumnName])
SELECT  DISTINCT  '['   CONVERT(VARCHAR(48), [TargetID])   ']'
FROM   TT;

DECLARE @PivotColumns VARCHAR(MAX), @TotalColumn VARCHAR(MAX), @SQL VARCHAR(MAX);

SET @PivotColumns = (SELECT STUFF((SELECT DISTINCT ', '   CONVERT(VARCHAR(50), [ColumnName])
                                   FROM   @ColumnsTable
                                   FOR XML PATH('')), 1, 2, ''));
SET @SQL = 'SELECT RowID,'  @PivotColumns  '
FROM   (
SELECT  RowID,TargetID,Actual                
        FROM   TT) AS t 
       PIVOT (MAX([Actual])
             FOR [TargetID] IN ('   @PivotColumns   ')) AS p';
EXEC(@SQL);