Multiples rows to columns with repeated rows

Question

I know that this subject is already well exposed here but I believe that I've a specific case and I didn't find any solutions so far.

I have the following result set:

Tp_Parameter    Value       Order_Id
------------    -----       --------    
Colour          Black       3824
Size            S           3824
Qty             2           3824
ItemId          101         3824
Colour          White       3824
Size            M           3824
Qty             1           3824
ItemId          102         3824
Colour          Red         3824
Size            L           3824
Qty             4           3824
ItemId          105         3824

And I'm looking for a result set like this:

Order_Id    ItemId  Colour  Size    Qty
--------    ------  ------  ----    ---
3824        101     Black   S       2
3824        102     White   M       1
3824        105     Red     L       4

I've tried with pivot, but I couldn't deal with the fact that it had to use aggregated functions, which one results in a one line result set (just the MAX or MIN etc etc)

Could you help me with that?

There is no logical connection between a particular ItemId and Colour. The colour Black could belong to any of those ItemIds. It looks like you're simply depending on the order they're currently displayed up there, but that won't work in a SQL table where the default ordering will be based upon clustered indexes. — Mitch
– Mitch, Commented Mar 1, 2017 at 23:56
You need at least one additional column to tie the rows together. Is there at least an id on the rows? — Gordon Linoff
– Gordon Linoff, Commented Mar 2, 2017 at 0:11
table design is wrong in many ways.even from your point of view table design is wrong. — KumarHarsh
– KumarHarsh, Commented Mar 2, 2017 at 5:15

Gordon Linoff · Accepted Answer · 2017-03-02 00:14:48Z

Let me assume that there is a unique, increasing id on each row. Then we can say that a specific "ItemId" finishes the rows for the item.

If so:

select order_id,
       max(case when tp_parameter = 'ItemId' then value end) as ItemId,
       max(case when tp_parameter = 'Colour' then value end) as Colour,
       max(case when tp_parameter = 'Size' then value end) as Size,
       max(case when tp_parameter = 'Qty' then value end) as Qty
from (select t.*,
             sum(case when tp_parameter = 'ItemId' then 1 else 0 end) over (partition by order_id order by <id> desc) as item_grp
      from t
     ) t
group by item_grp, order_id;

KumarHarsh · Accepted Answer · 2017-03-02 05:54:15Z

you can try this way too,

declare @t table(Tp_Parameter varchar(50)
,Value varchar(50),Order_Id int)

insert into @t VALUES

 ('Colour'   ,'Black',3824)
,('Size'   ,'S',3824)
,('Qty'   ,'2',3824)
,('ItemId'   ,'101',3824)
,('Colour'   ,'White',3824)
,('Size'   ,'M',3824)
,('Qty'   ,'1',3824)
,('ItemId'   ,'102',3824)
,('Colour'   ,'Red',3824)
,('Size'   ,'L',3824)
,('Qty'   ,'4',3824)
,('ItemId'   ,'105',3824)
;with CTE as
(
select *,ROW_NUMBER()over(order by (select NULL))rn
 from @t
 )
 select * 
 ,(select top 1 value from cte b 
 where tp_parameter='ItemId' and b.rn>=a.rn order by rn)MissingItemid
 from cte a

--now apply dynamic pivot so that you do not have to hard code parameter

yes, that pretty much worked for me. The key was the "ItemId" as a column (missingItemId). Thank you very much

Collectives™ on Stack Overflow

Multiples rows to columns with repeated rows

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related