数据表A ,字段A001 varchar(20)--单号,A002 varchar(8) -- 日期
数据表B ,字段B001 varchar(20)--单号,B002 int--序号,B003 varchar(10)--机器号, B004 numeric(14,4)-- 生产时数
数据如下:
------------------
A表:值
A001 A002
20090401001 20090401
20090401002 20090401
20090401003 20090401
20090401004 20090401
20090402001 20090402
20090402002 20090402
20090402003 20090402
20090402004 20090402
…… ……
--------------------
B表:值
B001 B002 B003 B004
20090401001 1 001 2
20090401002 1 002 1
20090401003 1 002 2
20090401004 1 003 4
20090402001 1 001 3
20090402002 1 002 2
20090402003 1 003 1
20090402004 1 001 2
--------------------
想得到如下查询
机器号(B003) 20090401 20090402 ……
001 2 5
002 3 2
003 4 1
……
说明:按日期查询机器的生产工时合计数,日期作为列名
数据表B ,字段B001 varchar(20)--单号,B002 int--序号,B003 varchar(10)--机器号, B004 numeric(14,4)-- 生产时数
数据如下:
------------------
A表:值
A001 A002
20090401001 20090401
20090401002 20090401
20090401003 20090401
20090401004 20090401
20090402001 20090402
20090402002 20090402
20090402003 20090402
20090402004 20090402
…… ……
--------------------
B表:值
B001 B002 B003 B004
20090401001 1 001 2
20090401002 1 002 1
20090401003 1 002 2
20090401004 1 003 4
20090402001 1 001 3
20090402002 1 002 2
20090402003 1 003 1
20090402004 1 001 2
--------------------
想得到如下查询
机器号(B003) 20090401 20090402 ……
001 2 5
002 3 2
003 4 1
……
说明:按日期查询机器的生产工时合计数,日期作为列名
P梁快来贴
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: #tb1
IF OBJECT_ID('tempdb.dbo.#tb1') IS NOT NULL DROP TABLE #tb1
CREATE TABLE #tb1 (A001 BIGINT,A002 DATETIME)
INSERT INTO #tb1
SELECT 20090401001,'20090401' UNION ALL
SELECT 20090401002,'20090401' UNION ALL
SELECT 20090401003,'20090401' UNION ALL
SELECT 20090401004,'20090401' UNION ALL
SELECT 20090402001,'20090402' UNION ALL
SELECT 20090402002,'20090402' UNION ALL
SELECT 20090402003,'20090402' UNION ALL
SELECT 20090402004,'20090402'
--> 生成测试数据: #tb2
IF OBJECT_ID('tempdb.dbo.#tb2') IS NOT NULL DROP TABLE #tb2
CREATE TABLE #tb2 (B001 BIGINT,B002 INT,B003 VARCHAR(3),B004 INT)
INSERT INTO #tb2
SELECT 20090401001,1,'001',2 UNION ALL
SELECT 20090401002,1,'002',1 UNION ALL
SELECT 20090401003,1,'002',2 UNION ALL
SELECT 20090401004,1,'003',4 UNION ALL
SELECT 20090402001,1,'001',3 UNION ALL
SELECT 20090402002,1,'002',2 UNION ALL
SELECT 20090402003,1,'003',1 UNION ALL
SELECT 20090402004,1,'001',2--SQL查询如下:DECLARE @sql VARCHAR(8000);
SET @sql='';SELECT
@sql=@sql+',SUM(CASE WHEN B.A002='''+CONVERT(VARCHAR(8),A002,112)+''' THEN A.B004 ELSE 0 END) AS ['+CONVERT(VARCHAR(8),A002,112)+']'
FROM #tb1 GROUP BY A002EXEC('SELECT A.B003'+@sql+' FROM #tb2 AS A LEFT JOIN #tb1 AS B ON A.B001=B.A001 GROUP BY A.B003')
-->> Author: 让你望见影子的墙(HEROWANG)生成测试数据 Date:2009-04-07 20:25:07
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
Go
CREATE TABLE tb(B001 varchar(20),B002 INT,B003 NVARCHAR(3),B004 INT)
Go
INSERT INTO tb
SELECT 20090401001,1,'001',2 UNION ALL
SELECT 20090401002,1,'002',1 UNION ALL
SELECT 20090401003,1,'002',2 UNION ALL
SELECT 20090401004,1,'003',4 UNION ALL
SELECT 20090402001,1,'001',3 UNION ALL
SELECT 20090402002,1,'002',2 UNION ALL
SELECT 20090402003,1,'003',1 UNION ALL
SELECT 20090402004,1,'001',2
GOSELECT * FROM TBwith
wang as (select b001=left(b001,8),b002,b003,b004= sum(b004) from tb group by left(b001,8),b002,b003 )select 机器号=b003,[20090401]=max(case when b001='20090401' then b004 end),
[20090401]=max(case when b001='20090402' then b004 end)
from wang
group by b003机器号 20090401 20090401
---- ----------- -----------
001 2 5
002 3 2
003 4 1
。(3 行受影响)
go
insert ta select '20090401001' , '20090401'
insert ta select '20090401002' , '20090401'
insert ta select '20090401003' , '20090401'
insert ta select '20090401004' , '20090401'
insert ta select '20090402001' , '20090402'
insert ta select '20090402002', '20090402'
insert ta select '20090402003', '20090402'
insert ta select '20090402004' , '20090402'
create table tb(B001 varchar(20),B002 int, B003 varchar(10),B004 int)
go
insert tb select '20090401001' , 1 , '001' , 2
insert tb select '20090401002' , 1 , '002' , 1
insert tb select '20090401003' , 1 , '002' , 2
insert tb select '20090401004' , 1 , '003' , 4
insert tb select '20090402001' , 1 , '001' , 3
insert tb select '20090402002' , 1 , '002' , 2
insert tb select '20090402003' , 1 , '003' , 1
insert tb select '20090402004' , 1 , '001' , 2
godeclare @sql varchar(8000)
set @sql='select b.B003'
select @sql=@sql+',sum(case a.A002 when '''+ A002 +''' then b.B004 else 0 end) ['+ A002 +']' from (select distinct A002 from ta) c
set @sql=@sql+' from tb b left join ta a on a.a001=b.b001 group by b.B003 order by b.b003'
--print @sql
exec (@sql)
go
/*
B003 20090401 20090402
---------- ----------- -----------
001 2 5
002 3 2
003 4 1
*/drop table ta,tb
go
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]
declare @sql varchar(1000)
set @sql='select UserName'
select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)aset @sql = @sql + ' from tb group by UserName'
print @sql
exec(@sql)
--讲解:--这个是第一次执行
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学],
--这个是第二次
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] ,
--这个是第三次
select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文]
--这个的数量来自于
(select distinct 课程 from tb)--这里只有3们课程create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb
我只说一个地方
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
你把这个看懂就明白了
例如当@id=1
select @str = @str + ',' + cast(value as varchar) from tb where id = 1
把满足id=1的str值通过','累加
当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后
在查询满足2的直到所有的ID完为止
这样明白了吧