SHU_TBL
id s_id
001 01
002 02
003 03SYS_TBL
s_id flg name
01 1 aa
01 2 ab
01 3 ac
02 1 ba
02 2 bb
02 3 bc
03 1 ca
03 2 cb
03 3 cc
我想得到结果。name1是(flg=1)的,name2(flg=2)
id s_id name1(flg=1) name2 name3
001 01 aa ab ac
002 02 ba bb bc
003 03 ca cb cc
id s_id
001 01
002 02
003 03SYS_TBL
s_id flg name
01 1 aa
01 2 ab
01 3 ac
02 1 ba
02 2 bb
02 3 bc
03 1 ca
03 2 cb
03 3 cc
我想得到结果。name1是(flg=1)的,name2(flg=2)
id s_id name1(flg=1) name2 name3
001 01 aa ab ac
002 02 ba bb bc
003 03 ca cb cc
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完为止
这样明白了吧
INSERT @TA
SELECT '001', '01' UNION ALL
SELECT '002', '02' UNION ALL
SELECT '003', '03'DECLARE @TB TABLE(s_id VARCHAR(2) , flg INT, name VARCHAR(2))
INSERT @TB
SELECT '01', 1, 'aa' UNION ALL
SELECT '01', 2, 'ab' UNION ALL
SELECT '01', 3, 'ac' UNION ALL
SELECT '02', 1, 'ba' UNION ALL
SELECT '02', 2, 'bb' UNION ALL
SELECT '02', 3, 'bc' UNION ALL
SELECT '03', 1, 'ca' UNION ALL
SELECT '03', 2, 'cb' UNION ALL
SELECT '03', 3, 'cc'SELECT A.ID,A.S_ID,
MAX(CASE WHEN FLG=1 THEN NAME ELSE '' END) AS NAME1,
MAX(CASE WHEN FLG=2 THEN NAME ELSE '' END) AS NAME2,
MAX(CASE WHEN FLG=3 THEN NAME ELSE '' END) AS NAME3
FROM @TA AS A JOIN @TB AS B ON A.S_ID=B.S_ID
GROUP BY A.ID,A.S_ID
/*
ID S_ID NAME1 NAME2 NAME3
----------- ---- ----- ----- -----
1 01 aa ab ac
2 02 ba bb bc
3 03 ca cb cc*/
(
id varchar(10),
s_id varchar(10)
)
insert into #SHU_TBL select '001','01'
union all select '002','02'
union all select '003','03'create table #SYS_TBL
(
s_id varchar(20),
flg int,
[name] varchar(20)
)
insert into #SYS_TBL select '01',1,'aa'
union all select '01',2,'ab'
union all select '01',3,'ac'
union all select '02',1,'ba'
union all select '02',2,'bb'
union all select '02',3,'bc'
union all select '03',1,'ca'
union all select '03',2,'cb'
union all select '03',3,'cc'select id,s_id,
max(case when flg=1 then [name] else null end ) 'name1',
max(case when flg=2 then [name] else null end ) 'name2',
max(case when flg=3 then [name] else null end ) 'name3'
from (select S.id,L.* from #SHU_TBL S join #SYS_TBL L on S.s_id=L.s_id) T
group by id,s_id
id s_id name1 name2 name3
---------- -------------------- -------------------- -------------------- --------------------
001 01 aa ab ac
002 02 ba bb bc
003 03 ca cb cc