Tranposing Rows to Columns (PIVOT) SQL

Question

I have a table of data that I am attempting to transpose/pivot rows to columns with.

I'm au fait with PIVOT/UNPIVOT and I tried to do this however due to the aggregation used with PIVOT I was only returning 1 result which was the first Endorsement "M06" and not the other 2 Endorsements for that PolRef@.

Here is my data example:

CREATE TABLE #temptable ( 
  [B@] int, 
  [key@] varchar(24), 
  [Ref@] varchar(6), 
  [PolRef@] varchar(10), 
  [Sequence@] int, 
  [Date] datetime, 
  [Endnumber] varchar(4), 
  [Desc] varchar(76), 
  [Value] int, 
  [Specdrivers] varchar(76), 
  [Reg] varchar(76)
)

INSERT INTO #temptable VALUES 
( 6, '484F445830314D4330310132', 'HODX01', 'HODX01MC01', 1050, N'2019-09-20T00:00:00', 'M06', 'Garaging/storage', 0, NULL, 'All' ), 
( 6, '484F445830314D433031013C', 'HODX01', 'HODX01MC01', 1060, N'2019-09-20T00:00:00', '046', 'NCB deleted', 0, NULL, 'All' ), 
( 6, '484F445830314D4330310146', 'HODX01', 'HODX01MC01', 1070, N'2019-09-20T00:00:00', '099', 'Limited mileage', 1500, NULL, 'All' )

DROP TABLE #temptable

Essentially I need a column for each row that says "Applicable Endorsement" for just the Endnumber value.

Output would look like:

|  B@ |   PolRef@   | Applicable Endorsement | Applicable Endorsement | Applicable Endorsement |
|  6  | HODX01MC01  |         M06            |         046            |         099            |

Any thoughts on how I can go about this, note there can be any number of endorsements it isn't a fixed amount.

So if you have 200 endorsement for same item, you want 200 new columns? A solution here is to use a dynamic pivot against a row-number for the endorsements. — EzLo, Sep 24 '19 at 13:38
@EzLo - I would say there is a ceiling of 30 endorsements, I don't envisage there to be even that many however its entirely possible. I didn't decide how this data should be received I'm just working towards a specification, I disagree entirely with the format but its not my call :) — Lynchie, Sep 24 '19 at 13:39
Why do you say that the problem with your attempt was "however due to the aggregation used with PIVOT I was only returning 1 row of data when I needed to return all 3.", when your posted desired output only has one row? — Tab Alleman, Sep 24 '19 at 13:53
@TabAlleman - Sorry i should be clearer - it only return 1 result which was the first Endorsement "M06" and not the other 2 Endorsements for that PolRef@ — Lynchie, Sep 24 '19 at 14:02

score 0 · Accepted Answer · answered Sep 24 '19 at 13:43

You can use the following PIVOT:

;WITH PrePivot AS
(
    SELECT
        T.B@,
        T.PolRef@,
        T.Endnumber,
        PivotRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.B@,
                T.PolRef@
            ORDER BY
                (SELECT NULL)) -- Determine the order here, maybe T.Date?
    FROM
        #temptable AS T
)
SELECT
    P.B@,
    P.PolRef@,
    [Applicable Endorsement] = P.[1],
    [Applicable Endorsement] = P.[2],
    [Applicable Endorsement] = P.[3],
    [Applicable Endorsement] = P.[4],
    [Applicable Endorsement] = P.[5]
FROM
    PrePivot AS V
    PIVOT (
        MAX(V.Endnumber) FOR V.PivotRanking IN ([1],[2],[3],[4],[5])
    ) AS P

I've written up to 5 endorsements, you can add as many as you want. If you want a dynamic amount, you need to use a dynamic pivot.

B@  PolRef@     Applicable Endorsement  Applicable Endorsement  Applicable Endorsement  Applicable Endorsement  Applicable Endorsement
6   HODX01MC01  M06                     046                     099                     NULL                    NULL

score 0 · Answer 2 · answered Sep 24 '19 at 13:49

Try this Dynamic Sql

IF OBJECT_ID('tempdb..#FormatedTable')IS NOT NULL
DROP TABLE #FormatedTable
Go

SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1)) As SeqId,'Applicable Endorsement'+CAST(ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS VARCHAR(100)) AS ReqColumn,*
INTO #FormatedTable
FROM #temptable

DECLARE  @Sql nvarchar(max),
         @DynamicColumn nvarchar(max),
         @MaxDynamicColumn nvarchar(max)


SELECT @DynamicColumn = STUFF((SELECT  ', '+QUOTENAME(ReqColumn)
FROM #FormatedTable  FOR XML PATH ('')),1,1,'') 

SELECT @MaxDynamicColumn = STUFF((SELECT  ', '+'MAX('+(QUOTENAME(ReqColumn))+') AS '+QUOTENAME(CAST(ReqColumn AS VARCHAR(100)))
FROM #FormatedTable ORDER BY SeqId   FOR XML PATH ('')),1,1,'') 
SELECT @MaxDynamicColumn
SET @Sql=' SELECT [B@],[PolRef@], '+ @MaxDynamicColumn+'
            FROM
            (
            SELECT * FROM #FormatedTable
            ) AS src
            PIVOT 
            (
            MAX(Endnumber) FOR [ReqColumn] IN ('+@DynamicColumn+')
            ) AS Pvt
            GROUP BY [B@],[PolRef@]
            '
PRINT (@Sql)
EXEC (@Sql)

Result

B@  PolRef@     Applicable Endorsement1  Applicable Endorsement2    Applicable Endorsement3
--------------------------------------------------------------------------------------------
6   HODX01MC01        M06                           046                         099

Tranposing Rows to Columns (PIVOT) SQL

2 Answers2

Result