脚本如下
create table tab
(
NumID int,
SkuID int,
Stock int
)
go
declare @i int
set @i=1
while @i<23
begin
insert into tab (NumID,SkuID,Stock) values ('23940',3982+@i,3+@i)
set @i=@i+1
end
declare @k int
set @k=1
while @k<4
begin
insert into tab (NumID,SkuID,Stock) values ('33950',32+@k,3+@k)
set @k=@k+1
end
declare @n int
set @n=1
while @n<2
begin
insert into tab (NumID,SkuID,Stock) values ('36951',52+@n,3+@n)
set @n=@n+1
end
求结果如下,注意:相同NumID中超过20条记录以后行转列过程中需要折分成两条记录
NumID Sku_Stock
-------------------------
23940 3983:4;3984:5;3985:6;3986:7;3987:8;3988:9;3989:10;3990:11;3991:12;3992:13;3993:14;3994:15;3995:16;3996:17;3997:18;3998:19;3999:20;4000:21;4001:22;4002:23
23940 4003:24;4004:25
33950 33:4;34:5;35:6
36951 53:4SQL SERVER行转列难
create table tab
(
NumID int,
SkuID int,
Stock int
)
go
declare @i int
set @i=1
while @i<23
begin
insert into tab (NumID,SkuID,Stock) values ('23940',3982+@i,3+@i)
set @i=@i+1
end
declare @k int
set @k=1
while @k<4
begin
insert into tab (NumID,SkuID,Stock) values ('33950',32+@k,3+@k)
set @k=@k+1
end
declare @n int
set @n=1
while @n<2
begin
insert into tab (NumID,SkuID,Stock) values ('36951',52+@n,3+@n)
set @n=@n+1
end
求结果如下,注意:相同NumID中超过20条记录以后行转列过程中需要折分成两条记录
NumID Sku_Stock
-------------------------
23940 3983:4;3984:5;3985:6;3986:7;3987:8;3988:9;3989:10;3990:11;3991:12;3992:13;3993:14;3994:15;3995:16;3996:17;3997:18;3998:19;3999:20;4000:21;4001:22;4002:23
23940 4003:24;4004:25
33950 33:4;34:5;35:6
36951 53:4SQL SERVER行转列难
精华帖里有了
WITH a1 AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY NumID ORDER BY @@servername) re
FROM #tab
)
,a2 AS
(
select NumID,SkuID,Stock,re-re%21 re
from a1
)
SELECT NumID,
STUFF((SELECT ';'+RTRIM(SkuID)+':'+RTRIM(Stock) FROM a2 WHERE NumID=a.NumID AND re=a.re FOR XML PATH('')),1,1,'')
FROM a2 a
GROUP BY NumID,re
--测试数据
create table tab
(
NumID int,
SkuID varchar(max),
Stock varchar(max)
)
go
declare @i int
set @i=1
while @i<23
begin
insert into tab (NumID,SkuID,Stock) values ('23940',3982+@i,3+@i)
set @i=@i+1
end
declare @k int
set @k=1
while @k<4
begin
insert into tab (NumID,SkuID,Stock) values ('33950',32+@k,3+@k)
set @k=@k+1
end
declare @n int
set @n=1
while @n<2
begin
insert into tab (NumID,SkuID,Stock) values ('36951',52+@n,3+@n)
set @n=@n+1
end--select * from tab--插入临时表
select NumID,SkuID,Stock,row_number() over(order by NumID) as rn,
(row_number() over(order by NumID)-1)/20 as kk into #tab from tab--select * from #tab
--得到结果
SELECT B.NumID,LEFT(StuList,LEN(StuList)-1) as Sku_Stock FROM (
SELECT NumID,
(SELECT SkuID+':'+Stock+',' FROM #tab
WHERE NumID=A.NumID and kk=A.kk
FOR XML PATH('')) AS StuList
FROM #tab A
GROUP BY NumID,kk
) B
drop table tab
drop table #tab
with t as
(select NumID,rtrim(SkuID)+':'+rtrim(Stock) 'Sku_Stock',
(row_number() over(partition by NumID order by getdate())-1)/20 'rn'
from tab)
select a.NumID,
stuff((select ';'+b.Sku_Stock from t b
where b.NumID=a.NumID and b.rn=a.rn
for xml path('')),1,1,'') 'Sku_Stock'
from t a
group by a.NumID,a.rn/*
NumID Sku_Stock
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23940 3983:4;3984:5;3985:6;3986:7;3987:8;3988:9;3989:10;3990:11;3991:12;3992:13;3993:14;3994:15;3995:16;3996:17;3997:18;3998:19;3999:20;4000:21;4001:22;4002:23
23940 4003:24;4004:25
33950 33:4;34:5;35:6
36951 53:4(4 row(s) affected)
*/
;WITH t AS
(
SELECT *,
(select count(*) from tab t2
where t2.numid = t1.numid and
t2.skuid <=t1.skuid) as rownum
FROM tab t1
),tt AS
(
select NumID,SkuID,Stock,rownum,
(rownum -1) / 20 as rownum_group
from t
)SELECT NumID,
STUFF(
(
SELECT ';'+cast(SkuID as varchar)+':'+cast(Stock as varchar)
FROM tt tt2
WHERE tt2.NumID=tt1.NumID AND
tt2.rownum_group = tt1.rownum_group
FOR XML PATH('')
),
1,1,''
) as sku_stock
FROM tt tt1
GROUP BY NumID,rownum_group
/*
NumID sku_stock
23940 3983:4;3984:5;3985:6;3986:7;3987:8;3988:9;3989:10;3990:11;3991:12;3992:13;3993:14;3994:15;3995:16;3996:17;3997:18;3998:19;3999:20;4000:21;4001:22;4002:23
23940 4003:24;4004:25
33950 33:4;34:5;35:6
36951 53:4
*/