在论坛中出现的比较难的sql问题：36(动态行转列 解析json格式字符串)

最近，在论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

sql经典行转列问题，外加点小难度。

http://bbs.csdn.net/topics/390636728

脚本如下：

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:4

我的解法：

;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
*/