sql_lover sql_lover - 7 months ago 11
SQL Question

Want to display the datas from two separate columns into a same column

Here is the input table:

input table

The child sku should be followed by parent sku below in the same column. Similarly all the products.

My code is not working. A different approach might help.

IF OBJECT_ID('tempdb..#tmp_table ') IS NOT NULL
DROP TABLE #tmp_table ;

select * into #tmp_table
from
(
select distinct sku,
B.[Child SKU] as childSKU,
C.[Parent SKU (PartNo) ] as parentSKU,
A.UPC,A.OutOfStockMsg,
A.Size
from (
select [Child SKU] as sku,
UPC,
OutOfStockMsg,
Size
from stg_LazyOne_ChildSKU_Details
union
select [Parent SKU (PartNo) ] as sku,
UPC,OutOfStockMsg,
Size
from stg_LazyOne_ChildSKU_Details
) A
left join stg_LazyOne_ChildSKU_Details B
on A.sku = B.[Child SKU] -- child
left join stg_LazyOne_ChildSKU_Details C
on A.sku = C.[Parent SKU (PartNo) ] -- parent
where B.[Child SKU] is null or C.[Parent SKU (PartNo) ] is null
) M

select *
from #tmp_table a


I want to display output like this (my expected result)
expected result

Answer
;WITH cte AS (
SELECT *
FROM (VALUES 
('M05L', 'M05', '840650030043', 'OUT OF STOCK', 'L'),
('M05M', 'M05', '840650030012', 'OUT OF STOCK', 'M'),
('M05S', 'M05', '840650030045', 'OUT OF STOCK', 'S'),
('B003L', 'B003', '840650030046', 'OUT OF STOCK', 'L'),
('B003M', 'B003', '840650030039', 'OUT OF STOCK', 'M'),
('B003S', 'B003', '840650030022', 'OUT OF STOCK', 'S')
) as t([Child SKU], [Parent SKU (PartNo)], [UPC], [OutOfStockMsg], [Size])
)
, skuuu AS (
SELECT  [Child SKU] as sku,
        [Parent SKU (PartNo)] as parent_sku,
        CASE WHEN [Size] = 'L' THEN 'size_pj=Large'
             WHEN [Size] = 'M' THEN 'size_pj=Medium'
             WHEN [Size] = 'S' THEN 'size_pj=Small'
             ELSE '' END AS [Size]
FROM cte
)


SELECT  sku,
        [Size] as addituional_attributes,
        NULL as configurable_variations
FROM skuuu
UNION ALL
SELECT DISTINCT  [Parent SKU (PartNo)] as sku
        ,NULL
        ,STUFF((SELECT  '|sku='+s.sku +','+ s.[Size]  FROM skuuu s WHERE s.parent_sku = c.[Parent SKU (PartNo)]FOR XML PATH ('')),1,1,'')
FROM cte c
ORDER BY sku DESC

Output:

sku   addituional_attributes configurable_variations
----- ---------------------- -----------------------------------------------------------------------------
M05S  size_pj=Small          NULL
M05M  size_pj=Medium         NULL
M05L  size_pj=Large          NULL
M05   NULL                   sku=M05L,size_pj=Large|sku=M05M,size_pj=Medium|sku=M05S,size_pj=Small
B003S size_pj=Small          NULL
B003M size_pj=Medium         NULL
B003L size_pj=Large          NULL
B003  NULL                   sku=B003L,size_pj=Large|sku=B003M,size_pj=Medium|sku=B003S,size_pj=Small

(8 row(s) affected)
Comments