question

endlessend2525 avatar image
endlessend2525 asked

Unpivot/Pivot of a line with multiple entries

Alternative of : https://www.reddit.com/r/SQLServer/comments/6l98cn/join_with_pivot_but_with_a_cartesian_product/ The data coming from a formular, the costumer fill up a row with different entry: Name of the ProductName / Date / Bar-Code / Price / Volume / Others But the problem is we get the data in the following way and each one in a different cell: OrderID | CostumerID | SellDate | ProductName1 | ProductName2 | ... | Bar-Code1 | Bar-Code2 |...| Price1 | Price2 |... You see that on the same line we can have multiple ProductName and hence multiple attributs of those article. Here under I created a table to add the union of two unpivot in order to have ProductsName1, ProductsName2, ...., Barcode1, Barcode2, `which were has column header in a column next the one containing the answer. But when I pivot it back in order to have the answer i get with the answer in the comment I have something strange. An orderID may contain different products. With the solution I get for each orderID all the products and its details but with mine I only get the first product and details per Order ID. So I loose all information for the orderID who contains more than one product. Do you see a solution on how I could improve my code ? /*** Check if the temporary table #mc table exist if yes, drop it ***/ IF OBJECT_ID('tempdb..#mC') IS NOT NULL DROP TABLE #mC /*** Create a temporary table called #mc ***/ CREATE TABLE #mC ( [ORDER_ID] bigint , [CostumerID] int , [SellDate] date , RecordLable nvarchar(255) , Record nvarchar(255) ); /*** Insert the following in the temporary table #mc ***/ INSERT INTO #mC /*** Unpivoting from F012_K1Z1 to F012_K2 because those are nvarchar(250)***/ SELECT [ORDER_ID] , [CostumerID] , [SellDate] , RecordLable , Record FROM ( /*** Select the source table ***/ SELECT [ORDER_ID] , [CostumerID] , [SellDate] ,[ProductName1] ,[ProductName2] --- ,[ProductName30] ,[BarCode1] ,[BarCode2] --- ,[BarCode25] FROM Table ) AS D /*** Create a table with [ORDER_ID], [CostumerID], [SellDate],a column called Recordlable with the name of the column of the data and a column which contains the info for the specific ORDER_ID RecordLable etc. ***/ UNPIVOT ( Record FOR RecordLable IN (ProductName1] ,[ProductName2], ... ,[ProductName30],[BarCode1],[BarCode2], ...,[BarCode25] ) ) AS DD UNION ALL SELECT [ORDER_ID] , [CostumerID] , [SellDate] , RecordLable , Record = CAST( Record AS nvarchar(30)) FROM ( SELECT [ORDER_ID] , [CostumerID] , [SellDate],[ObjectA1] ,[ObjectA2], ...,[ObjectA30],[ObjectB1],[ObjectB2],[ObjectB30] FROM Table ) AS D UNPIVOT ( Record FOR RecordLable IN ( [ObjectA1], [ObjectA2], ... , [ObjectA30], [ObjectB1], [ObjectB2], ..., [ObjectB30] ) ) AS DD SELECT * FROM #mc ORDER BY /*** Try to pivot the RecordLable with the corresponding Record**/ SELECT [ORDER_ID] ,[CostumerID] ,[SellDate] ,[ProductName1] ,[BarCode1] ,[ObjectA1] ,[ObjectB1] FROM ( SELECT [ORDER_ID] ,[CostumerID] ,[SellDate] ,RecordLable, Record FROM #mC) up PIVOT (MAX(Record) FOR RecordLable IN ( [ProductName1],[ProductName2],... ,[BarCode1],[BarCode2],...,[ObjectA1], [ObjectA2],...,[ObjectB1], [ObjectB2],... ) ) as piv ORDER BY [ORDER_ID]
pivotunion
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.