Richard Lloyd
asked on
SQL Server Pivot
Hi
I have table of data (several million lines long) in SQLServer.
The table has 3 columns, StockId, CustomField and CustomData. There is a primary index over the 3 fields (i.e. all rows are unique)
I need to convert the table to a pivotted table as follows:
StockId, CustomField1, CustomField2, CustomField3, CustomField4,....
========================== ========== ========== ========== =
123456, CustomData1, CustomData2, CustomData3, CustomData4,...
There are an unknow number of CustomFields.
Can anyone help!
I have table of data (several million lines long) in SQLServer.
The table has 3 columns, StockId, CustomField and CustomData. There is a primary index over the 3 fields (i.e. all rows are unique)
I need to convert the table to a pivotted table as follows:
StockId, CustomField1, CustomField2, CustomField3, CustomField4,....
==========================
123456, CustomData1, CustomData2, CustomData3, CustomData4,...
There are an unknow number of CustomFields.
Can anyone help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Quick follow question. How would I run this to save the results as a new table?
It's pretty simple:
CREATE TABLE #Sample
(
StockId INT ,
CustomField INT ,
CustomData NVARCHAR(255)
);
INSERT INTO #Sample
VALUES ( 1, 1, '1' ),
( 1, 2, '2' ),
( 2, 2, '3' ),
( 3, 3, '4' ),
( 4, 4, '5' );
DECLARE @Columns NVARCHAR(MAX) = STUFF(( SELECT ', ' + QUOTENAME(CAST(S.CustomField AS sysname))
FROM #Sample S
GROUP BY S.CustomField
FOR XML PATH('')
), 1, 2, '');
DECLARE @CreateC NVARCHAR(MAX) = STUFF(( SELECT ', ' + QUOTENAME(CAST(S.CustomField AS sysname)) + ' NVARCHAR(255)'
FROM #Sample S
GROUP BY S.CustomField
FOR XML PATH('')
), 1, 2, '');
DECLARE @SqlC NVARCHAR(MAX) = 'CREATE TABlE PivotTable ( StockID INT, @CreateC );';
SET @SqlC = REPLACE(@SqlC, '@CreateC', @CreateC);
EXECUTE (@SqlC);
DECLARE @Sql NVARCHAR(MAX) = '
INSERT INTO PivotTable
SELECT *
FROM #Sample S
PIVOT ( MAX(S.CustomData) FOR S.CustomField IN ( @Columns ) ) P;
';
SET @Sql = REPLACE(@Sql, '@Columns', @Columns);
EXECUTE (@Sql);
DROP TABLE #Sample;
ASKER
Thanks
ank you
ank you
ASKER
BTW I had change the sum to a max as the customdata is not numeric. But you would not have known.