'Creating rows when column name repeats for pivot

I have a CSV file that imports into SQL in the below format, theres a key field then repeating values that need to be on seperate rows, each new part needs to be a new row can anyone suggest some SQL to help, I've managed to pivot the data however this only returns the first parts data.

Current CSV IMPORT

Header Data
Load number 220511
Part 1234
Lot AB14
Qty 10
Part 4567
Lot HD14
Qty 19

Current Pivot

Load Number Part Lot QTY
220511 1234 AB14 10

Required Pivot

Load Number Part Lot QTY
220511 1234 AB14 10
220511 4567 HD14 19

Current Code

Select [Load number ],

[part number ],
[lot number ],
[quantity ]

From


(Select LTRIM(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Header,'Item',''),0,''),1,''),2,''),3,''),4,''),5,''),6,''),7,''),8,''),9,'')) as HEADER, Data From (Select LTRIM(SUBSTRING([column1],1,CASE CHARINDEX(':', [column1])WHEN 0
                THEN LEN([column1])
            ELSE CHARINDEX(':', [column1]) - 1
            END)) AS HEADER ,LTRIM(SUBSTRING([column1], CASE CHARINDEX(':', [column1])
            WHEN 0
                THEN LEN([column1]) + 1
            ELSE CHARINDEX(':', [column1]) + 1
            END, 1000)) AS DATA FROM [BCW_TREAT].[dbo].[DMSIMPORTLOAD]) as d
            Where HEADER = 'Load number' or HEADER like '%part%' or HEADER like '%lot%'or HEADER like '%quantity%' or Data > '0' or Data <> '""') b
            pivot
            (max(DATA)
  for HEADER in ([Load number ],

[part number ],
[lot number ],
[quantity ])) piv
sql-server


Solution 1:[1]

based on this question TSQL Pivot without aggregate function, all we have to do is to create a column like CustomerID, that's what the rn column do, so the result would be:

SELECT t.Data [Load number]
    ,Part
    ,Lot
    ,Qty
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY Header ORDER BY Header
            ) AS rn
    FROM #temp
    ) AS SourceTable
PIVOT(max(data) FOR Header IN (
            [Load number]
            ,[Part]
            ,[Lot]
            ,[Qty]
            )) AS PivotTable
JOIN #temp t ON Header = 'Load number'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dordi

Related Questions