问一个行专列的问题

[muyebo]

数据如下
dt(时间表)
sj
20100601
20100602
20100603
spxs(商品销售表)
spmc spdm sj sl
锅 001 20100601 1
盆 002 20100601 1
碗 003 20100601 1
锅 001 20100602 1
碗 003 20100602 1
sp(商品表)
spmc spdm
锅 001
盆 002
碗 003
柜 004

想要的结果
sj spdm1 sl1 spdm2 sl2 spdm3 sl3 spdm4 sl4
20100601 001 1 002 1 003 1 004 0
20100602 001 1 002 0 003 1 004 0
20100603 001 0 002 0 003 0 004 0

creat dt(sj varchar(14))
insert into tb(sj) values('20100601')
insert into tb(sj) values('20100602')
insert into tb(sj) values('20100603')
go
creat sale(spmc varchar(14),spbm varchar(14),sj varchar(14)) 
insert into tb(spmc,spbm,sj) values('锅','001','20100601')
insert into tb(spmc,spbm,sj) values('盆','002','20100601')
insert into tb(spmc,spbm,sj) values('碗','003','20100601')
insert into tb(spmc,spbm,sj) values('锅','001','20100602')
insert into tb(spmc,spbm,sj) values('碗','003','20100602')
go
creat sale(spmc varchar(14),spbm varchar(14))
insert into tb(spmc,spbm) values('锅','001')
insert into tb(spmc,spbm) values('盆','002')
insert into tb(spmc,spbm) values('碗','003')
insert into tb(spmc,spbm) values('柜','004')
go

[muyebo]

应该是列转行

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2010-06-08 10:33:10
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------

--> 生成测试数据表: [dt]
IF OBJECT_ID('[dt]') IS NOT NULL
	DROP TABLE [dt]
GO
CREATE TABLE [dt] ([sj] [datetime])
INSERT INTO [dt]
SELECT '20100601' UNION ALL
SELECT '20100602' UNION ALL
SELECT '20100603'

--> 生成测试数据表: [spxs]
IF OBJECT_ID('[spxs]') IS NOT NULL
	DROP TABLE [spxs]
GO
CREATE TABLE [spxs] ([spmc] [nvarchar](10),[spdm] [nvarchar](10),[sj] [datetime],[sl] [int])
INSERT INTO [spxs]
SELECT '锅','001','20100601','1' UNION ALL
SELECT '盆','002','20100601','1' UNION ALL
SELECT '碗','003','20100601','1' UNION ALL
SELECT '锅','001','20100602','1' UNION ALL
SELECT '碗','003','20100602','1'

--> 生成测试数据表: [sp]
IF OBJECT_ID('[sp]') IS NOT NULL
	DROP TABLE [sp]
GO
CREATE TABLE [sp] ([spmc] [nvarchar](10),[spdm] [nvarchar](10))
INSERT INTO [sp]
SELECT '锅','001' UNION ALL
SELECT '盆','002' UNION ALL
SELECT '碗','003' UNION ALL
SELECT '柜','004'

--SELECT * FROM [dt]
--SELECT * FROM [spxs]
--SELECT * FROM [sp]
/*
sj spdm1 sl1 spdm2 sl2 spdm3 sl3 spdm4 sl4
20100601 001 1 002 1 003 1 004 0
20100602 001 1 002 0 003 1 004 0
20100603 001 0 002 0 003 0 004 0
*/
-->SQL查询如下:

SELECT sj=CONVERT(VARCHAR(8),a.sj,112),
	spdm1='001',
	sl1=SUM(CASE c.spdm WHEN '001' THEN 1 ELSE 0 END),
	spdm1='002',
	sl1=SUM(CASE c.spdm WHEN '002' THEN 1 ELSE 0 END),
	spdm1='003',
	sl1=SUM(CASE c.spdm WHEN '003' THEN 1 ELSE 0 END),
	spdm1='004',
	sl1=SUM(CASE c.spdm WHEN '004' THEN 1 ELSE 0 END)
FROM dt a
	LEFT JOIN spxs b 
		ON a.sj=b.sj
	LEFT JOIN sp c
		ON b.spdm=c.spdm
GROUP BY CONVERT(VARCHAR(8),a.sj,112)
/*
sj       spdm1 sl1         spdm1 sl1         spdm1 sl1         spdm1 sl1
-------- ----- ----------- ----- ----------- ----- ----------- ----- -----------
20100601 001   1           002   1           003   1           004   0
20100602 001   1           002   0           003   1           004   0
20100603 001   0           002   0           003   0           004   0

(3 行受影响)
*/

[htl258_Tony]

SELECT sj=CONVERT(VARCHAR(8),a.sj,112),
	spdm1='001',
	sl1=SUM(CASE c.spdm WHEN '001' THEN 1 ELSE 0 END),
	spdm2='002',
	sl2=SUM(CASE c.spdm WHEN '002' THEN 1 ELSE 0 END),
	spdm3='003',
	sl3=SUM(CASE c.spdm WHEN '003' THEN 1 ELSE 0 END),
	spdm4='004',
	sl4=SUM(CASE c.spdm WHEN '004' THEN 1 ELSE 0 END)
FROM dt a
	LEFT JOIN spxs b 
		ON a.sj=b.sj
	LEFT JOIN sp c
		ON b.spdm=c.spdm
GROUP BY CONVERT(VARCHAR(8),a.sj,112)
/*
sj       spdm1 sl1         spdm2 sl2         spdm3 sl3         spdm4 sl4
-------- ----- ----------- ----- ----------- ----- ----------- ----- -----------
20100601 001   1           002   1           003   1           004   0
20100602 001   1           002   0           003   1           004   0
20100603 001   0           002   0           003   0           004   0

(3 行受影响)
*/

modify

[Java技术栈]

哇 楼上真强 膜拜。。

[htl258_Tony]

--动态SQL

-->SQL查询如下:
DECLARE @s VARCHAR(8000)
SELECT @s=ISNULL(@s,'')+',spdm'+LTRIM(rn)+'='''+spdm+''',sl'
		 +LTRIM(rn)+'=SUM(CASE c.spdm WHEN '''+spdm+''' THEN 1 ELSE 0 END)'
FROM (SELECT rn=(SELECT COUNT(1) FROM [sp] WHERE [spdm]<=t.[spdm]),* FROM [sp] t) t
EXEC('
	SELECT sj=CONVERT(VARCHAR(8),a.sj,112)'+@s+'
	FROM dt a
		LEFT JOIN spxs b 
			ON a.sj=b.sj
		LEFT JOIN sp c
			ON b.spdm=c.spdm
	GROUP BY CONVERT(VARCHAR(8),a.sj,112)
')
/*
sj       spdm1 sl1         spdm2 sl2         spdm3 sl3         spdm4 sl4
-------- ----- ----------- ----- ----------- ----- ----------- ----- -----------
20100601 001   1           002   1           003   1           004   0
20100602 001   1           002   0           003   1           004   0
20100603 001   0           002   0           003   0           004   0

(3 行受影响)
*/

[muyebo]

htl258再请教下,这个商品表sp的数量是未知的,可能不会刚好四个

[muyebo]

强啊,佩服