CODEWITHSUNDEEP
sqlsql-server
sql_lover
sql_lover

Reputation: 127

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) enter image description here

Upvotes: 2

Views: 77

Answers (1)

gofr1
gofr1

Reputation: 15977

;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)

Upvotes: 1

Related Questions