David Rogers David Rogers - 3 months ago 8
SQL Question

Consolidating and Pivoting Data in SQL

I have a data source that describes transactions for sales formatted like this:

Transaction | Date | Location | UPC | LineNumber
----------------+-----------+----------+-----------+------------
123 | 7/2/2016 | Store A | 123456789 | 1
123 | 7/2/2016 | Store A | 123965478 | 2
124 | 7/2/2016 | Store A | 123456789 | 1
124 | 7/2/2016 | Store A | 123459879 | 2
124 | 7/2/2016 | Store A | 123456789 | 3
123 | 7/3/2016 | Store B | 123456789 | 1
123 | 7/3/2016 | Store B | 958685458 | 2


In order to use this data in our reporting tools, I need to format this with the Transaction-Location as Unique and the UPC following on as columns based on line number:

Transaction | Date | UPC 1 | UPC 2 | UPC 3
------------+-----------+-----------+----------
123-Store A | 7/2/2016 | 123456789 | 123965478 | NULL
124-Store A | 7/2/2016 | 123456789 | 123459879 | 123456789
123-Store B | 7/3/2016 | 123456789 | 958685458 | NULL


Any thoughts are appreciated

Answer

Standard PIVOT will do the trick. You just have to define your concatenated Transaction column in a CTE or nested Select before calling PIVOT.

DECLARE @Table AS TABLE ([Transaction] INT, Date DATE, Location VARCHAR(15), UPC INT, LineNumber INT)
INSERT INTO @Table ([Transaction], Date, Location, UPC, LineNumber)
VALUES
(123,'7/2/2016','Store A',123456789,1)
,(123,'7/2/2016','Store A',123965478,2)
,(124,'7/2/2016','Store A',123456789,1)
,(124,'7/2/2016','Store A',123459879,2)
,(124,'7/2/2016','Store A',123456789,3)
,(123,'7/3/2016','Store B',123456789,1)
,(123,'7/3/2016','Store B',958685458,2)

;WITH cteCombineTransLocation AS (
    SELECT
       CAST([Transaction] AS VARCHAR(50)) + '-' + Location as [Transaction]
       ,Date
       ,UPC
       ,LineNumber
    FROM
       @Table
)

SELECT
    [Transaction]
    ,[Date]
    ,[1] as UPC1
    ,[2] as UPC2
    ,[3] as UPC3
FROM
    cteCombineTransLocation
    PIVOT (
       MAX(UPC)
       FOR LineNumber IN ([1],[2],[3])
    ) p

If you want more UPC columns or if that number is dynamic then as the commenters pointed out you can use dynamic sql, but you will still need to prepare your concatenated field first.