表数据:
部门 室内保洁 室外保洁 (其他列....)
天山物管站 3 4 ..
风华物管站 2 5 ...
...... ... .... .....
(其他物管站...)要求显示成
工作内容 天山物管站 风华物管站 (其他物管站...)
室内保洁
室外保洁
(其他列....)除了用 动态拼接 sql 的方法,还有其他更好的吗?
部门 室内保洁 室外保洁 (其他列....)
天山物管站 3 4 ..
风华物管站 2 5 ...
...... ... .... .....
(其他物管站...)要求显示成
工作内容 天山物管站 风华物管站 (其他物管站...)
室内保洁
室外保洁
(其他列....)除了用 动态拼接 sql 的方法,还有其他更好的吗?
解决方案 »
- 多台Server建立SQL Server镜像失败
- 关于一个创建表格的问题
- 关于设计简洁有效的数据库
- 如何写一个存储过程来显示表中数据?
- 执行一条sql语句出现错误,请高手解决。
- 大量的查询结果怎么处理
- 用sql语句怎么把同一表按照列data1,data2,data3的最小的那个按时间从小到大排序?
- SQL2000 服务器有问题?
- 200分!关于SQL脚本从SQL SERVER向ORACLE转化的问题(非常着急,只要能解决问题可以再开帖加分)http://www.csdn.net/expert/topic/776/7
- SQL server安装问题...
- 对存取过程编译的一个疑惑
- sql 里面case when else 的使用问题,
--行列互转
--摘自中国风博客,引用请标明内容来源
--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
*/
参考资料
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([部门] VARCHAR(10),[室内保洁] INT,[室外保洁] INT)
INSERT [tb]
SELECT '天山物管站',3,4 UNION ALL
SELECT '风华物管站',2,5
--------------开始查询--------------------------SELECT 工作内容,SUM(天山物管站) AS 天山物管站,SUM(风华物管站) AS 风华物管站 FROM (
SELECT '室内保洁' AS 工作内容,
CASE [部门] WHEN '天山物管站' THEN [室内保洁] ELSE 0 END AS 天山物管站,
CASE [部门] WHEN '风华物管站' THEN [室内保洁] ELSE 0 END AS 风华物管站
FROM [tb]
UNION ALL
SELECT '室外保洁' AS 工作内容,
CASE [部门] WHEN '天山物管站' THEN [室内保洁] ELSE 0 END AS 天山物管站,
CASE [部门] WHEN '风华物管站' THEN [室内保洁] ELSE 0 END AS 风华物管站
FROM [tb]
) t
GROUP BY 工作内容
----------------结果----------------------------
/*
工作内容 天山物管站 风华物管站
-------- ----------- -----------
室内保洁 3 2
室外保洁 3 2(2 行受影响)*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]
(
[部门] VARCHAR(10) ,
[室内保洁] INT ,
[室外保洁] INT
)
INSERT [tb]
SELECT '天山物管站', 3, 4
UNION ALL
SELECT '风华物管站', 2, 5
--------------开始查询--------------------------
DECLARE @a VARCHAR(MAX),@b VARCHAR(MAX),@sql VARCHAR(MAX)
SELECT @a=ISNULL(@a+',','')+QUOTENAME(NAME) FROM sys.columns WHERE [object_id]=object_id('tb') AND name NOT IN ('部门')
SELECT @b=ISNULL(@b+',','')+ QUOTENAME([部门]) FROM (SELECT DISTINCT [部门] FROM tb) t
SELECT @sql='
SELECT * FROM
(
SELECT [部门],[工作内容],[数量] FROM
(SELECT * FROM [tb]) p
UNPIVOT
(数量 FOR [工作内容] IN ('+@a+'))AS unpvt
) T
PIVOT
(SUM(数量) FOR [部门] in ('+@b+'))AS pvt
'
--PRINT @sql
EXEC(@sql)