如何实现该查询？？？
tb table
city 地区 户口
北京 西城 城镇
北京 西城 乡村
北京 东城 城镇
北京 东城 乡村
北京 西城 乡村
上海 浦东 乡村
上海 浦东 城镇
上海 浦东 城镇
上海 浦西 城镇
上海 浦西 乡村
结果
地区 北京 上海 城镇 乡村
西城 3 2 2 1
东城 2 2 1 1
浦东 0 0 2 1
浦西 0 0 1 1
以前都是使用游标处理，现在突然感觉应该可以使用sql直接写出来，请高手指点!
[解决办法]
--行列互转
--1、行换列
if object_id('Class') is not null
drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='
+quotename([Course],'''')+' then [Score] else 0 end)'
from
Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
--动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')
--生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
--生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78
(2 行受影响)
*/
go
--加上总成绩(学科平均分)
--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))
生成动态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]
go
--2005方法:
动态:
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')
--生成静态:
select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337
(2 行受影响)
*/
go
--2、列转行
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go
--2000:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]
go
--2005:
动态:
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')
go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
*/
这个很全面，自己模仿着些
[解决办法]
declare @tb table (city varchar(4),地区 varchar(4),户口 varchar(4))
insert into @tb
select '北京','西城','城镇' union all
select '北京','西城','乡村' union all
select '北京','东城','城镇' union all
select '北京','东城','乡村' union all
select '北京','西城','乡村' union all
select '上海','浦东','乡村' union all
select '上海','浦东','城镇' union all
select '上海','浦东','城镇' union all
select '上海','浦西','城镇' union all
select '上海','浦西','乡村'
select 地区,
北京=sum(case when city='北京' then 1 else 0 end),
上海=sum(case when city='上海' then 1 else 0 end),
城镇=sum(case when 户口='城镇' then 1 else 0 end),
乡村=sum(case when 户口='乡村' then 1 else 0 end)
from @tb group by 地区
/*
地区 北京 上海 城镇 乡村
---- ----------- ----------- ----------- -----------
东城 2 0 1 1
浦东 0 3 2 1
浦西 0 2 1 1
西城 3 0 1 2
*/
[解决办法]
/*
insert tbl
select '北京','西城','城镇' union all
select '北京','西城','乡村' union all
select '北京','东城','城镇' union all
select '北京','东城','乡村' union all
select '北京','西城','乡村' union all
select '上海','浦东','乡村' union all
select '上海','浦东','城镇' union all
select '上海','浦东','城镇' union all
select '上海','浦西','城镇' union all
select '上海','浦西','乡村'
结果
地区 北京 上海 城镇 乡村
西城 3 2 2 1
东城 2 2 1 1
浦东 0 0 2 1
浦西 0 0 1 1
*/
create table tbl(
city nvarchar(10),
area nvarchar(10),
addr nvarchar(10)
)
select area,
北京=sum(case when city='北京' then 1 else 0 end),
上海=sum(case when city='上海' then 1 else 0 end),
城镇=sum(case when addr='城镇' then 1 else 0 end),
乡村=sum(case when addr='乡村' then 1 else 0 end)
from tbl group by area
/*
结果表：
area北京上海城镇乡村
东城2011
浦东0321
浦西0211
西城3012
*/
[解决办法]
declare @tb table (city varchar(4),地区 varchar(4),户口 varchar(4))insert into @tbselect '北京','西城','城镇' union allselect '北京','西城','乡村' union allselect '北京','东城','城镇' union allselect '北京','东城','乡村' union allselect '北京','西城','乡村' union allselect '上海','浦东','乡村' union allselect '上海','浦东','城镇' union allselect '上海','浦东','城镇' union allselect '上海','浦西','城镇' union allselect '上海','浦西','乡村'select 地区, sum(case when city='北京' then 1 else 0 end)as 北京,sum(case when city='上海' then 1 else 0 end) as 上海,sum(case when 户口='城镇' then 1 else 0 end)as 城镇,sum(case when 户口='乡村' then 1 else 0 end) as 乡村 from @tb group by 地区(10 行受影响)地区 北京 上海 城镇 乡村---- ----------- ----------- ----------- -----------东城 2 0 1 1浦东 0 3 2 1浦西 0 2 1 1西城 3 0 1 2(4 行受影响)
[解决办法]
declare @tb table (city varchar(4),地区 varchar(4),户口 varchar(4))insert into @tbselect '北京','西城','城镇' union allselect '北京','西城','乡村' union allselect '北京','东城','城镇' union allselect '北京','东城','乡村' union allselect '北京','西城','乡村' union allselect '上海','浦东','乡村' union allselect '上海','浦东','城镇' union allselect '上海','浦东','城镇' union allselect '上海','浦西','城镇' union allselect '上海','浦西','乡村'select 地区,北京=sum(case when city='北京' then 1 else 0 end),上海=sum(case when city='上海' then 1 else 0 end),城镇=sum(case when 户口='城镇' then 1 else 0 end),乡村=sum(case when 户口='乡村' then 1 else 0 end)from @tb group by 地区/*地区 北京 上海 城镇 乡村---- ----------- ----------- ----------- -----------东城 2 0 1 1浦东 0 3 2 1浦西 0 2 1 1西城 3 0 1 2*/
[解决办法]
楼主要求的结果有问题啊，上海没有“西城”、“东城”。
[解决办法]
declare @tb table (city varchar(4),地区 varchar(4),户口 varchar(4))
insert into @tb
select '北京','西城','城镇' union all
select '北京','西城','乡村' union all
select '北京','东城','城镇' union all
select '北京','东城','乡村' union all
select '北京','西城','乡村' union all
select '上海','浦东','乡村' union all
select '上海','浦东','城镇' union all
select '上海','浦东','城镇' union all
select '上海','浦西','城镇' union all
select '上海','浦西','乡村'
select 地区, sum(case when city='北京' then 1 else 0 end)as 北京,
sum(case when city='上海' then 1 else 0 end) as 上海,
sum(case when 户口='城镇' then 1 else 0 end)as 城镇,
sum(case when 户口='乡村' then 1 else 0 end) as 乡村
from @tb
group by 地区
