行列转换合并分拆-白红宇

/*
 1 
*/
declare
@sql
varchar( 
8000) 
set
@sql
=
'
select 
''
总量
''
 as [时间] 
'
select
@sql
=
@sql
+
'
 , sum(case 时间 when 
'
+
rtrim(时间)  
+
'
 then 总量 end) [
'
+
rtrim(时间)  
+
'
]
'
from ( 
select
DATEPART(hh, CreateTime) 时间, 
count( 
*) 总量  
from Business_Login  
WHERE  CreateTime  
> (  
select
CONVERT( 
varchar,  
getdate(),  
111 )) 
GROUP
BY
DATEPART(hh, CreateTime) 
 )t  
ORDER
BY 时间 
set
@sql
=
@sql
+
'
 from (select DATEPART(hh, CreateTime) 时间,count(*) 总量   from Business_Login 
 WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
  group by DATEPART(hh, CreateTime) 
 )  t 
'
exec( 
@sql)  
/*
 2 
*/
select
DATEPART(hh, CreateTime) 时间, 
count( 
*) 总量  
into #tb   
from Business_Login  
WHERE  CreateTime  
> (  
select
CONVERT( 
varchar,  
getdate(),  
111 )) 
GROUP
BY
DATEPART(hh, CreateTime) 
declare
@sql
varchar( 
8000) 
set
@sql
=
'
select 
''
总量
''
 as [时间] 
'
select
@sql
=
@sql
+
'
 , sum(case 时间 when 
'
+
rtrim(时间)  
+
'
 then 总量 end) [
'
+
rtrim(时间)  
+
'
]
'
from #tb  
ORDER
BY 时间 
set
@sql
=
@sql
+
'
 from #tb  t 
'
exec( 
@sql)  
DROP
TABLE #tb 
/*
 3 
*/
declare
@s
nvarchar( 
4000) 
select
@s
=
isnull( 
@s
+
'
,
', 
'') 
+
quotename( 
[
时间
]) 
+
'
=sum(case when [时间]=
'
+
quotename( 
[
时间
], 
'''') 
+
'
 then [总量] else 0 end)
'
from
 ( 
select
DATEPART(hh, CreateTime) 时间, 
count( 
*) 总量  
from Business_Login  
WHERE  CreateTime  
> (  
select
CONVERT( 
varchar,  
getdate(),  
111 )) 
GROUP
BY
DATEPART(hh, CreateTime))t 
group
by
[
时间
]
ORDER
BY 时间 
exec( 
'
select 
''
总量
''
 as [时间], 
'
+
@s
+
'
 from (
 select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
 WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
 GROUP BY  DATEPART(hh, CreateTime)
 )t 
') 
/*
 4 
*/
declare
@s
nvarchar( 
4000) 
select
@s
=
isnull( 
@s
+
'
,
', 
'') 
+
quotename( 
[
时间
])  
from ( 
select
DATEPART(hh, CreateTime) 时间, 
count( 
*) 总量  
from Business_Login  
WHERE  CreateTime  
> (  
select
CONVERT( 
varchar,  
getdate(),  
111 )) 
GROUP
BY
DATEPART(hh, CreateTime)) t  
group
by
[
时间
]
ORDER
BY 时间 
exec( 
'
select * from (select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
 WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
 GROUP BY  DATEPART(hh, CreateTime)  
  )t pivot (max([总量]) for [时间] in(
'
+
@s
+
'
))b
') 
/*
 时间    9    10    11    12    13    14    15    16    17    18
 总量    187    369    340    56    374    381    307    410    516    73
*/
--
按0,1,2...
SELECT
DATEPART(hh, CreateTime)  
[
hour
], 
COUNT( 
*) cnt 
FROM   Business_Login 
WHERE  CreateTime  
> (  
select
CONVERT( 
varchar,  
getdate(),  
111 )) 
GROUP
BY
DATEPART(hh, CreateTime) 
ORDER
BY
[
hour
]
/*
 hour    cnt
 9    187
 10    369
 11    340
 12    56
 13    374
 14    381
 15    307
 16    410
 17    516
 18    73
*/

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:

姓名课程分数

张三语文 74

张三数学 83

张三物理 93

李四语文 74

李四数学 84

李四物理 94

想变成(得到如下结果)：

姓名语文数学物理

---- ---- ---- ----

李四 74 84 94

张三 74 83 93

-------------------

createtable tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insertinto tb values('张三' , '语文' , 74)

insertinto tb values('张三' , '数学' , 83)

insertinto tb values('张三' , '物理' , 93)

insertinto tb values('李四' , '语文' , 74)

insertinto tb values('李四' , '数学' , 84)

insertinto tb values('李四' , '物理' , 94)

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名 as 姓名 ,

max(case 课程 when'语文'then 分数 else0end) 语文,

max(case 课程 when'数学'then 分数 else0end) 数学,

max(case 课程 when'物理'then 分数 else0end) 物理

from tb

groupby 姓名

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare@sqlvarchar(8000)

set@sql='select 姓名 '

select@sql=@sql+' , max(case 课程 when '''+ 课程 +''' then 分数 else 0 end) ['+ 课程 +']'

from (selectdistinct 课程 from tb) as a

set@sql=@sql+' from tb group by 姓名'

exec(@sql)

--SQL SERVER 2005 静态SQL。

select*from (select*from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

--SQL SERVER 2005 动态SQL。

declare@sqlvarchar(8000)

select@sql=isnull(@sql+'],[' , '') + 课程 from tb groupby 课程

set@sql='['+@sql+']'

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in ('+@sql+')) b')

---------------------------------

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名语文数学物理平均分总分

---- ---- ---- ---- ------ ----

李四 74 84 94 84.00 252

张三 74 83 93 83.33 250

--SQL SERVER 2000 静态SQL。

select 姓名姓名,

max(case 课程 when'语文'then 分数 else0end) 语文,

max(case 课程 when'数学'then 分数 else0end) 数学,

max(case 课程 when'物理'then 分数 else0end) 物理,

cast(avg(分数*1.0) asdecimal(18,2)) 平均分,

sum(分数) 总分

from tb

groupby 姓名

--SQL SERVER 2000 动态SQL。

declare@sqlvarchar(8000)

set@sql='select 姓名 '

select@sql=@sql+' , max(case 课程 when '''+ 课程 +''' then 分数 else 0 end) ['+ 课程 +']'

from (selectdistinct 课程 from tb) as a

set@sql=@sql+' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql)

--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

(select*from (select*from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,

(select 姓名 , cast(avg(分数*1.0) asdecimal(18,2)) 平均分 , sum(分数) 总分 from tb groupby 姓名) n

where m.姓名 = n.姓名

--SQL SERVER 2005 动态SQL。

declare@sqlvarchar(8000)

select@sql=isnull(@sql+',' , '') +'['+ltrim(课程名称)+']'from tb groupby 课程

exec ('select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in ('+@sql+')) b) m ,

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')

droptable tb

ALTERPROCEDURE[dbo].[pro_score]

declare@xqvarchar(20)

declare@zyvarchar(20)

declare@sqlvarchar(1000)

select@sql=isnull(@sql+',' , '') +'['+ltrim(课程名称)+']'from score_view groupby 课程名称

exec ('select m.*, n.平均分,n.总分 from (select * from (select 学号,姓名,学期,专业,课程名称,成绩 from score_view ) a

pivot (max(成绩) for 课程名称 in ('+@sql+') ) b ) m ,

(select 学号, 学期,专业,cast(avg(成绩*1.0) as decimal(18,2))平均分,sum(成绩)总分 from score_view group by 学号,学期,专业 having 学期=''20081'' and 专业=''软件技术'') n

where m.学号 = n.学号 ')

--20081 -- @xq

------------------

问题：如果上述两表互相换一下：即表结构和数据为：

姓名语文数学物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)：

姓名课程分数

---- ---- ----

李四语文 74

李四数学 84

李四物理 94

张三语文 74

张三数学 83

张三物理 93

--------------

createtable tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insertinto tb values('张三',74,83,93)

insertinto tb values('李四',74,84,94)

--SQL SERVER 2000 静态SQL。

select*from

(

select 姓名 , 课程 ='语文' , 分数 = 语文 from tb

unionall

select 姓名 , 课程 ='数学' , 分数 = 数学 from tb

unionall

select 姓名 , 课程 ='物理' , 分数 = 物理 from tb

) t

orderby 姓名 , case 课程 when'语文'then1when'数学'then2when'物理'then3end

--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare@sqlvarchar(8000)

select@sql=isnull(@sql+' union all ' , '' ) +' select 姓名 , [课程] = '+quotename(Name , '''') +' , [分数] = '+quotename(Name) +' from tb'

from syscolumns

where name! = N'姓名'and ID =object_id('tb') --表名tb，不包含列名为姓名的其它列

orderby colid asc

exec(@sql+' order by 姓名 ')

--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

--------------------

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名课程分数

---- ------ ------

李四语文 74.00

李四数学 84.00

李四物理 94.00

李四平均分 84.00

李四总分 252.00

张三语文 74.00

张三数学 83.00

张三物理 93.00

张三平均分 83.33

张三总分 250.00

------------------

select*from

(

select 姓名 as 姓名 , 课程 ='语文' , 分数 = 语文 from tb

unionall

select 姓名 as 姓名 , 课程 ='数学' , 分数 = 数学 from tb

unionall

select 姓名 as 姓名 , 课程 ='物理' , 分数 = 物理 from tb

unionall

select 姓名 as 姓名 , 课程 ='平均分' , 分数 =cast((语文 + 数学 + 物理)*1.0/3asdecimal(18,2)) from tb

unionall

select 姓名 as 姓名 , 课程 ='总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

orderby 姓名 , case 课程 when'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end

droptable tb

/*表结构，数据如下：

id value

----- ------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

需要得到结果：

id values

------ -----------

1 aa,bb

2 aaa,bbb,ccc

即：group by id, 求 value 的和（字符串相加） */

1. 旧的解决方法(在sql server 2000中只能用函数解决。)

--======================================================

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')

--1. 创建处理函数

CREATE FUNCTION dbo.f_strUnite(@id int)

RETURNS varchar(8000)

BEGIN

DECLARE @str varchar(8000)

SET @str = ''

SELECT @str = @str + ',' + value FROM tb WHERE id=@id

RETURN STUFF(@str, 1, 1, '')

END

-- 调用函数

SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id

drop table tb

drop function dbo.f_strUnite

id value

----------- -----------

1 aa,bb

2 aaa,bbb,ccc

（所影响的行数为 2 行）

--======================================================

2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。)

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')

-- 查询处理

SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(

SELECT [values]= STUFF(REPLACE(REPLACE(

(

SELECT value FROM tb N

WHERE id = A.id

FOR XML AUTO

), ' <N value="', ','), '"/>', ''), 1, 1, '')

drop table tb

id values

----------- -----------

1 aa,bb

2 aaa,bbb,ccc

(2 行受影响)

--SQL2005中的方法2

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')

select id, [values]=stuff((select ','+[value] from tb t where id=tb.id

for xml path('')), 1, 1, '')

from tb

group by id

id values

----------- --------------------

1 aa,bb

2 aaa,bbb,ccc

drop table tb

/*有表tb, 如下:

id value

----------- -----------

1 aa,bb

2 aaa,bbb,ccc

欲按id,分拆value列, 分拆后结果如下:

id value

----------- --------

1 aa

1 bb

2 aaa

2 bbb

2 ccc */

1. 旧的解决方法(sql server 2000)

select a.id, substring(a.[value], b.number, charindex(',', a.[value] + ',', b.number) - b.number)

FROM tb a, master..spt_values b

WHERE b.type='p' and substring(',' + a.[value],b.number, 1) = ','

2. 新的解决方法(sql server 2005)

create table tb(id int,value varchar(30))

insert into tb values(1,'aa,bb')

insert into tb values(2,'aaa,bbb,ccc')

SELECT a.id, b.value

FROM(

SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLaCE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb

OUTER aPPLY(

SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v)

DROP TabLE tb

id value

----------- ------------------------------

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 行受影响)

本文转自曾祥展博客园博客，原文链接：http://www.cnblogs.com/zengxiangzhan/archive/2010/01/03/1638185.html，如需转载请自行联系原作者