Unpivot and Pivot does not return data

Question

I'm trying to return data as columns.

I've written this unpivot and pivot query:

`select StockItemCode, barcode, barcode2 from (select StockItemCode, col+cast(seq as varchar(20)) col, value   from  (
select 
  (select min(StockItemCode) 
   from RTLBarCode t2
   where t.StockItemCode = t2.StockItemCode) StockItemCode, 
  cast(BarCode as varchar(20)) barcode, 
     row_number() over(partition by StockItemCode order by StockItemCode) seq
from RTLBarCode t) d  unpivot(
value
for col in (barcode)  ) unpiv) src pivot (  max(value)  for col in (barcode, barcode2)) piv;`

But the problem is only the "Barcode2" field are returning a value (the barcode field returns a null when in fact there is a value.

SAMPLE DATA

I have a Table called RTLBarCode It has a field called Barcode and a field called StockItemCode

For StockItemCode = 10 I have 2 rows with a Barcode value of 5014721112824 and 0000000019149.

Can anyone see where I am going wrong?

Many thanks

Answer 1

You are indexing your barcode in unpiv. This results in col's-values barcode1 and barcode2.

But then you are pivoting on barcode instead of barcode1. No value is found and the aggregate returns null.

The correct statement would be:

select StockItemCode, barcode1, barcode2 from 
(
    select StockItemCode, col+cast(seq as varchar(20)) col, value   
    from  
    (
        select 
                (select min(StockItemCode)from RTLBarCode t2 where t.StockItemCode = t2.StockItemCode) StockItemCode,  
                cast(BarCode as varchar(20)) barcode, 
                row_number() over(partition by StockItemCode order by StockItemCode) seq
        from RTLBarCode t
     ) d  
     unpivot(value for col in (barcode)) unpiv
) src 
pivot (max(value) for col in (barcode1, barcode2)) piv