select 字段1 , (case when 字段2='1001' then 字段3 else '' end ) as '1001',
(case when 字段2='1002' then 字段3 else '' end ) as '1002',
(case when 字段2='1003' then 字段3 else '' end ) as '1003',
(case when 字段2='1004' then 字段3 else '' end ) as '1004' from 表
(case when 字段2='1002' then 字段3 else '' end ) as '1002',
(case when 字段2='1003' then 字段3 else '' end ) as '1003',
(case when 字段2='1004' then 字段3 else '' end ) as '1004' from 表
(case when 字段2='1002' then 字段3 else '' end ) as '1002',
(case when 字段2='1003' then 字段3 else '' end ) as '1003',
(case when 字段2='1004' then 字段3 else '' end ) as '1004' from 表
group by 字段1
sum(case when 字段2='1001' then 字段3 end) as [1001],
sum(case when 字段2='1002' then 字段3 end) as [1002],
sum(case when 字段2='1003' then 字段3 end) as [1003],
sum(case when 字段2='1004' then 字段3 end) as [1004],
from talbename
group by 字段1另,示例数据中没有字段1与字段2都相同的行,看不出你的组合要求是怎样的。
还有,注意少用错别字。你的标题好吓人。
(select 字段3 from 表 a3 where a3.字段1=a.字段1 and 字段2=1003) as [1003] from (select distinct 字段1 from 表)a
create table #Tmp(a varchar(3),b int,c int)
insert #Tmp
select 'aaa',1001,1465
union all select 'bbb',1002,1124
union all select 'ccc',1003,1257
union all select 'ddd',1004,4855
union all select 'aaa',1004,1144select * from #TmpSELECT a,
[1001] = MAX(CASE WHEN b = 1001 THEN c ELSE null END),
[1002] = MAX(CASE WHEN b = 1002 THEN c ELSE null END),
[1003] = MAX(CASE WHEN b = 1003 THEN c ELSE null END),
[1004] = MAX(CASE WHEN b = 1004 THEN c ELSE null END)
FROM #Tmp
GROUP BY a
ORDER BY aDROP TABLE #Tmp/*
-- RESULT:
a b c
---- ----------- -----------
aaa 1001 1465
bbb 1002 1124
ccc 1003 1257
ddd 1004 4855
aaa 1004 1144(所影响的行数为 5 行)a 1001 1002 1003 1004
---- ----------- ----------- ----------- -----------
aaa 1465 NULL NULL 1144
bbb NULL 1124 NULL NULL
ccc NULL NULL 1257 NULL
ddd NULL NULL NULL 4855(所影响的行数为 4 行)
*/
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '+b+' THEN c END)'
from(select distinct b=cast(b as varchar) from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '+b+' THEN cast(c as varchar) else '''' END)'
from(select distinct b=cast(b as varchar) from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')
go
create table 表(a varchar(3),b int,c int)
insert 表
select 'aaa',1001,1465
union all select 'bbb',1002,1124
union all select 'ccc',1003,1257
union all select 'ddd',1004,4855
union all select 'aaa',1004,1144
go--查询处理
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '+b+' THEN cast(c as varchar) else '''' END)'
from(select distinct b=cast(b as varchar) from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')
go--删除测试表
DROP TABLE 表/*--测试结果a 1001 1002 1003 1004
---- ---------- ---------- ---------- ----------
aaa 1465 1144
bbb 1124
ccc 1257
ddd 4855
--*/
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '+b+' THEN cast(c as varchar) else '''' END)'
from(select distinct b=cast(b as varchar) from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')
set nocount off
go--调用:
exec p_qry
create proc p_qry
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+b+']=MAX(CASE b when '+b+' THEN cast(c as varchar) else '''' END)'
from(select distinct b=cast(b as varchar) from 表) a
exec('SELECT a'+@s+' FROM 表 GROUP BY a ORDER BY a')
set nocount off
go--调用:
exec p_qry
试试这个语句:declare @sql varchar(8000)
set @sql='select 字段1,'
select @sql=@sql+',min(case 字段2 when ''' + cast(字段2 as varchar(4)) +''' then 字段3 else null end) as ['+字段2+']' from (select distinct 字段2 from table1) t1
set @sql=@sql+'from table1 group by 字段1'
exec (@sql)
这里得到了一种行转列的通用方法!