商品SKU mySQL 列转行_SQL行列转换——商品SKU颜色尺码合并

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(100))SizeName,row from Roy where row=1

union all

select a.Number,a.ColorName,

cast(b.SizeName+','+a.SizeName as nvarchar(100)),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 (MAXRECURSION 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(100))ColorName,row from Roy where row=1

union all

select a.Number,a.SizeName,

cast(b.ColorName+','+a.ColorName as nvarchar(100)),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)