Link to home
Start Free TrialLog in
Avatar of Richard Lloyd
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!
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Lloyd
Richard Lloyd

ASKER

Great job. Works a treat.

BTW I had change the sum to a max as the customdata is not numeric. But you would not have known.
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;

Open in new window

Thanks
ank you