TABLE:
ID BT SJ
1 满意 2004-10-01
1 不满意 2004-09-01
1 一般 2004-08-01
2 满意 2004-10-01
2 不满意 2004-09-01
2 一般 2004-08-01
最后生成报表
ID 满意 不满意 一般
1 2004-10-01 2004-09-01 2004-08-01
2 2004-10-01 2004-09-01 2004-08-01
select [ID],(select sj from test as t where t.id=s.id and t.bt='满意') as 满意,(select sj from test as t where t.id=s.id and t.bt='不满意') as 不满意,(select sj from test as t where t.id=s.id and t.bt='一般') as 一般 from test as s group by s.id
select id,
满意=max(case when bt='满意' then sj end)
,不满意=max(case when bt='不满意' then sj end)
,一般=max(case when bt='一般' then sj end)
from tb
group by id
--测试
create table tb(ID int,BT varchar(6),SJ varchar(10) )
insert into tb
select 1,'满意','2004-10-01' union
select 1,'不满意','2004-09-01' union
select 1,'一般', '2004-08-01' union
select 2,'满意', '2004-10-01' union
select 2,'不满意','2004-09-01' union
select 2,'一般', '2004-08-01'
---查询
select id,
满意=max(case when bt='满意' then sj end)
,不满意=max(case when bt='不满意' then sj end)
,一般=max(case when bt='一般' then sj end)
from tb
group by id
--删除测试表
drop table tb
--结果:
id 满意 不满意 一般
----------- ---------- ---------- ----------
1 2004-10-01 2004-09-01 2004-08-01
2 2004-10-01 2004-09-01 2004-08-01
(所影响的行数为 2 行)
--------------------------------------------
select a.[id],a.sj as '满意',b.sj as '不满意',c.sj as '一般'
from
(select [id],sj from table1 where bt='满意') a
full join
(select [id],sj from table1 where bt='不满意') b
on a.[id2]=b.[id2]
full join
(select [id2],sj from table1 where bt='一般') c
on a.[id2]=c.[id2]
222222222222222222222222222222222222222222
已知ID,如何从ID得到table1 B列中的值并把它转换为行?
(table1)
A B.ID B.VALUE
-------------------
ID P1 P1V
ID P2 P2V
ID P3 P3V
.. ..
(table2)
YGID B.ID B.VALUE
---------------------
001 P1 P1V1
001 P2 P2V1
001 P3 P3V1
001 P1 P1V2
002 P1 ...
--->
YGID P1 P2 P3 ...
-----------------------------
001 xxx xxx xxx xxx
001 xxx xxx xxx xxx
002 xxx xxx xxx xxx
參考一個例子
create table tb(名稱 varchar(10),數量 numeric(10),類型 varchar(5))
Insert into tb
select 'L001','1','A'
union all select 'L001','2','B'
union all select 'L002','5','C'
union all select 'L003','6','D'
union all select 'L004','9','A'
union all select 'L004','5','D'
select * from tb
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+max(類型)+']=sum(case 類型 when '''+max(類型)+''' then 數量 else 0 end)'
from tb group by 類型
print @sql
exec('select 名稱'+@sql+' from tb group by 名稱')
--結果
名稱 A B C D
---------------------------------------
L001 1 2 0 0
L002 0 0 5 0
L003 0 0 0 6
L004 9 0 0 5
------
用动态语句来解决:
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case [B.ID] when '''+[B.ID]+''' then [B.VALUE] else '' end) as ['+[B.ID]+']'
from table1
group by [B.ID]
exec('select YGID'+@sql+' from table2 group by YGID')
--没有测试
---------------
--B.VALUE字段应该是字符串型 的吧,改一下。
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case [B.ID] when '''+[B.ID]+''' then [B.VALUE] else '' end) as ['+[B.ID]+']'
from table1
group by [B.ID]
exec('select YGID'+@sql+' from table2 group by YGID')
----------------------------
--建表
create table table1([A] varchar(10),[B.ID] varchar(10),[B.Value] varchar(10))
insert into table1 select
'ID' , 'P1' , 'P1V'
union all select
'ID' , 'P2' , 'P2V'
union all select
'ID' , 'P3' , 'P3V'
create table table2(YGID varchar(10), [B.ID] varchar(10), [B.VALUE] varchar(10))
insert into table2 select
'001' , 'P1' , 'P1V1'
union all select
'001' , 'P2' , 'P2V1'
union all select
'001' , 'P3' , 'P3V1'
union all select
'001' , 'P1' , 'P1V2'
union all select
'002' , 'P1' , '...'
--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case [B.ID] when '''+[B.ID]+''' then [B.VALUE] else '''' end) as ['+[B.ID]+']'
from table1
group by [B.ID]
exec('select YGID'+@sql+' from table2 group by YGID')
--结果:
001 P1V2 P2V1 P3V1
002 ...