Creating rows when column name repeats for pivot

Question

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

How do you know that the `Part` `Lot`, and `QTY` lines belong to that load number? Furthermore how do you know that part `1234` belongs in the same row as lot `AB14`? I see no relationship in the data, and the order of the columns in a table aren't reliable enough to establish relationship (they would be reliable in a CSV and it might be that you need to perform this tranformation before ingesting into the DB). — JNevill, May 16 '22 at 15:06
Hi @JNevill the system that creates the CSV only creates 1 CSV per load number, each load number can have upto 12 parts. The CSV is structured the same for each load. The CSV needs loading into SQL automatically for the ERP system to book them in. — Tinman100, May 17 '22 at 06:19
Right. But there is still no data present in the table that establishes row order. Without at least a column to suggest how these rows should be ordered, there is no way to do what you are wanting. So you have two options 1) Build into whatever automated process loads this into a table, a step, before it's loaded, to make this data relational (it currently is not relational). 2) Build into whatever automated process loads this into a table, a step that generates a row number from the CSV row to add to a new column in the table so order can be established. — JNevill, May 17 '22 at 14:10

score 0 · Answer 1 · answered May 17 '22 at 09:53

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'

`PARTITION BY HEADER ORDER BY HEADER` to establish ordering is going to cause problems. — JNevill, May 17 '22 at 14:07

Creating rows when column name repeats for pivot

1 Answers1