SQl查询问题(50分)

有一个表名：仓库<br>编号 &nbsp;仓库名称<br>1 仓库A<br>2 仓库B<br>３ 仓库C<br><br>第二个表：库存表<br><br>商品编号 品名　　仓库编号　　数量<br>&nbsp;1001 铅笔 　　1 10<br>　1001 铅笔 　　2 6<br><br>要求得到如下查询结果：<br><br>商品编号 品名　　总库存　　仓库A数量 仓库B数量　　仓库C数量<br>&nbsp;1001 铅笔 　　16 10　　　　6　　　　　　0<br><br>并且查询结果可以随着仓库的多少自动增减仓库数量的列。比如仓库有４个，查询结果就显示仓库A到仓库D，仓库表中有５个仓库时，查询结果就会显示为仓库A到仓库E

CREATE TABLE ck(dm varchar(10), mc varchar(20))<br>GO<br>INSERT INTO ck(dm, mc) select '1', &nbsp; '仓库A'<br>UNION ALL SELECT '2', '仓库B'<br>UNION ALL SELECT '3', '仓库C'<br>GO<br>CREATE TABLE kc(bh varchar(10), mc varchar(20),<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ckdm varchar(10), sl numeric(18, 2))<br>GO <br>INSERT INTO kc(bh, mc, ckdm, sl) <br>SELECT '1001','铅笔', '1', &nbsp;10<br>UNION ALL SELECT '1001', '铅笔', '2', 6<br>SELECT * FROM KC<br>GO<br>SELECT * FROM ck<br>GO<br><br>DECLARE @s varchar(8000)<br>SELECT @s = ''<br>SELECT @s = @s + ',' + char(13) + <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'[' + mc + '] = sum(case kc.ckdm when ''' + dm + ''' then isnull(sl, 0) else 0 end)'<br>FROM ck<br>PRINT @s <br><br>exec('SELECT bh, kc.mc , sum(isnull(sl, 0)) sl' + @s + ' FROM ck, kc where ck.dm = kc.ckdm group by kc.bh, kc.mc')<br>GO<br>//------------------结果.<br>1001 铅笔 16.00 10.00 6.00 .00

也可以这样.<br>DECLARE @s varchar(8000)<br>SELECT @s = ''<br>SELECT @s = @s + ',' + char(13) + <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'[' + mc + '] = sum(case ck.mc when ''' + mc + ''' then isnull(sl, 0) else 0 end)'<br>FROM ck<br>PRINT @s <br><br>exec('SELECT bh, kc.mc , sum(isnull(sl, 0)) sl' + @s + ' FROM ck, kc where ck.dm = kc.ckdm group by kc.bh, kc.mc')<br>GO

看不懂呵，能不能解释一下！

很基本的行转列.

动态生成query语句不行吗？