create table #sgs(OrderID int, OrderCount int, Product varchar(50))
;with cte
as
(
select '1419253' as OrderID, 1 OrderCount ,'Jackets' Product union
select '1419258', 1 ,'Jackets' union
select '1419258', 1 ,'Trousers' union
select '1419259', 1 ,'Trousers' union
select '1419259', 1 ,'Jackets' union
select '1419259', 1 ,'waistcoat' union
select '1419266', 1 ,'Tie' union
select '1419266', 1 ,'waistcoat' union
select '1419266', 1 ,'Jackets' union
select '1419266', 1 ,'Trousers'
)
insert into #sgs
select distinct OrderID,
sum(OrderCount),
case
when r.Product in ('Jackets','Trousers') then 'Suits'
else r.Product
end as Product
from cte r
group by OrderID, Product
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@Piv as NVARCHAR(MAX)
SELECT @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[' + a.Product + '], '
FROM ( SELECT DISTINCT Product
FROM #sgs r ) a
ORDER BY a.Product
FOR XML PATH ( '' ) ) b ( Piv );
print @cols
set @query = 'SELECT ' + @cols + ' from
(
select Product,
OrderCount
from #sgs
) x
pivot
(
count(OrderCount)
for Product in (' + @cols + ')
) p '
select @query
SELECT [Suits], [waistcoat], [Tie]
from (
select Product,
OrderCount
from #sgs) x
pivot ( count(OrderCount) for Product in ([Suits], [Tie], [waistcoat])) p
execute(@query)
drop table #sgs
Thank You yasiaz. It's working like a charm. But I am not able to understand your query.
I would appreciate if you could explain the below, please?
SELECT @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[' + a.Product + '], '
FROM ( SELECT DISTINCT Product
FROM #sgs r ) a
ORDER BY a.Product
FOR XML PATH ( '' ) ) b ( Piv );
print @cols
set @query = 'SELECT ' + @cols + ' from
(
select Product,
OrderCount
from #sgs
) x
pivot
(
count(OrderCount)
for Product in (' + @cols + ')
) p '
select @query
SELECT [Suits], [waistcoat], [Tie]
from (
select Product,
OrderCount
from #sgs) x
pivot ( count(OrderCount) for Product in ([Suits], [Tie], [waistcoat])) p
yes that is if you want to do it dynamically. lets say you have more products and you want to this dynamically.
Look at the result of
select @query
I do notice though there is a problem with the solution provided. it does count Jackets and/or Trousers as Suits when there is an uneven pair.