SQL row and column conversions--Commodity SKU Color Size Merge

CREATE PROCEDURE Proc_goodsskucombine
As
IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ GOODSTEMP1] ') and type in (N ' U '))
DROP TABLE [dbo]. GoodsTemp1


; with Roy as
(select Number,colorname,sizename,row=row_number () over (partition by Number,colorname order by Number,colorname) from V _jskc_bysku)
, Roy2 as
(Select Number,colorname,
Cast (sizename as nvarchar) Sizename,row from Roy where row=1
UNION ALL
Select A.number,a.colorname,
Cast (b.sizename+ ', ' +a.sizename as nvarchar ()), A.row from Roy a join Roy2 b
On A.number=b.number and A.colorname=b.colorname and a.row=b.row+1)
Select Number,colorname,sizename
Into GOODSTEMP1
From Roy2 A
Where row= (select Max (row) from Roy where Number=a.number and Colorname=a.colorname) Order by Number,colorname option (max Recursion 0)


IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ GOODSTEMP2] ') and type in (N ' U '))
DROP TABLE [dbo]. GoodsTemp2


; with Roy as
(select Number,colorname,sizename,row=row_number () over (partition by number order by number) from GOODSTEMP1)
, Roy2 as
(Select Number,sizename,
Cast (colorname as nvarchar) Colorname,row from Roy where row=1
UNION ALL
Select A.number,a.sizename,
Cast (b.colorname+ ', ' +a.colorname as nvarchar ()), A.row from Roy a join Roy2 b
On A.number=b.number and A.row=b.row+1)
Select Number,colorname,sizename
Into GOODSTEMP2
From Roy2 A
Where row= (select Max (row) from Roy where number=a.number) Order by number option (Maxrecursion 0)

SQL row and column conversions--Commodity SKU Color Size Merge