【转】SQL Server 2000/2005/2008 行列互相转换、合并等总结

转自：http://hi.baidu.com/1987raymond/blog/item/6ed57f168282745920a4e987.html

在数据库查询中，我们经常需要将数据库中的行与列互相转换；本文将对这些转换进总结，以供参考。

1, 同行多列数据转换为一列

创建一个测试表，并且添加测试数据：

--Create test table 1
create table test1
(
id int not null identity primary key,
c1 varchar(100),
c2 varchar(100),
c3 varchar(100)
)
go
--Add test data for table test1
insert into test1 values('I am ','split by ','these three columns.')
insert into test1 values('Could you ','please change me to be',' a full sentence?')

执行语句后，数据表中的数据如下：

id	c1	c2	c3
1	I am	split by	these three columns.
2	Could you	please change me to be	a full sentence?

任务：将c1,c2,c3这三列链接起来，可以通过下面的语句：

select c1+c2+c3 as [FullSentence] from test1

输出结果如下：

FullSentence
I am split by these three columns.
Could you please change me to be a full sentence?
(2 row(s) affected)

2, 同列多行数据转换为一条数据

创建一个测试表，并且添加测试数据：

--Create test table 2
create table test2
(
id int not null identity primary key,
c varchar(100)
)
go
insert into test2 values('I am ')
insert into test2 values('split by ')
insert into test2 values('these three rows.')

执行语句后，数据表中的数据如下：

id          c
----------- ----------------------------------------------------------------------------------------------------
1           I am
2           split by
3           these three rows.
(3 row(s) affected)

任务：将这三行的数据转换为一行数据，通过下面的语句：

方法1：定义临时的varchar变量，通过游标一条条读取，然后在循环中改变临时变量的值最终输出；关于游标的方法，这里再叙述。

方法2：直接通过SQL语句

在我们编程中，可能经常用到String或者StringBuilder，在循环中改变他们的值，在SQL语句中我们可以使用类似的方法

declare @result varchar(1000)
set @result=''
select @result=@result+c from test2
print @result

执行语句，输出：I am split by these three rows.

3,将多行单列数据转换为一行多列数据

创建一个测试表，并且添加测试数据：

create table test3
(
id int not null identity primary key,
student varchar(50),
class varchar(100),
score int default(60) null
)
go
insert into test3 values('Lee','Chinese',70)
insert into test3 values('Lee','Math',80)
insert into test3 values('Lee','English',90)
insert into test3 values('Lew','Chinese',60)
insert into test3 values('Lew','Math',95)
insert into test3 values('Lew','English',97)

执行语句后，数据表中的数据如下：

id	student	class	score
1	Lee	Chinese	70
2	Lee	Math	80
3	Lee	English	90
4	Lew	Chinese	60
5	Lew	Math	95
6	Lew	English	97

任务：按照学生名字将其各科成绩按列输出。

在SQL Server 2005以及2008或者以上版本时，我们可以使用Pivot语句，PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性，详情参看：http://technet.microsoft.com/zh-cn/library/ms177410.aspx

语句：

select r.* from
(select student,score,class from test3)
as t
pivot
(
max(t.score)
for t.class in ([Chinese],[Math],[English])
)
as r

运行结果：

 

Table

studen	Chinese	Math	English
Lee	70	80	90
Lew	60	95	97

在SQL Server 2000中，由于不支持PIVOT语句，因此我们需要用到CASE When 语句。

 declare @sql varchar(8000)
set @sql='student'
select top 3 @sql=@sql++','+quotename([class])+'=max( case when [class]='+quotename([class],'''')
            +'then [score] else null end)' from test3
set @sql='select '+@sql +' from test3 group by student'
exec(@sql)
print @sql

 

Table

studen	Chinese	Math	English
Lee	70	80	90
Lew	60	95	97

运行结果一致，而@sql最终为：

select student,[Chinese]=max( case when [class]='Chinese'then [score] else null end),[Math]=max( case when [class]='Math'then [score] else null end),[English]=max( case when [class]='English'then [score] else null end) from test3 group by student

直接执行这个语句也可以得到同样的效果，只不过一般情况下，我们不清楚到底有多少class，因此需要先组装我们的SQL语句，然后动态的执行。

在SQL Server 2000中另外还得提到的问题和注意事项

没有varchar(max)与nvarchar(max)类型，因此如果有很多的class以至于我们的sql语句超出过了最大的长度，那么最终就会出错，因为SQL会被截断，而这种情况我就遇到过。我们明白exec sp_executesql 只能最长接受nvarchar(4000)的SQL语句，因为在一些需要使用参数的情况下，我们可能需要将参数的值直接包含在sql语句中，改用exec(@sql1+@sql2+@sql3+...+@sqlN)的方式执行，就不会受到长度的限制，只不过需要我们自己将这些@sql片段的变量进行有效的分配，而这可能需要用到case when等语句。在我遇到的情况中，我定义了三个变量解决了我遇到的问题。

declare @sql_select_fragment_1 varchar(8000)
declare @sql_select_fragment_2 varchar(8000)
declare @sql_select_fragment_3 varchar(8000)

另外一点，由于varchar类型在SQL Server 2000中只能接受最大8000个单字节字符，因此，我们不能先定义一个变量，然后将上面三个变量连接在一起赋值给它，而只能在调用的时候直接用+连接，否则也会被阶段为8000个单字节字符或者4000个双字节字符。

4,将一行多列数据转换为一列多行数据

在SQL Server 2005/2008或者以上版本，我们可以使用Unpivot语句来实现。

如下面的SQL语句，将第3中的结果再拆分为行：

select * into test4 from
(select student,score,class from test3)
as t
pivot
(
max(t.score)
for t.class in ([Chinese],[Math],[English])
)
as r;
select * from test4;
select * from
(select * from test4) as t
unpivot
(
score for class in ([Chinese],[Math],[English])
)
as r

运行结果：

Table

student	score	class
Lee	70	Chinese
Lee	80	Math
Lee	90	English
Lew	60	Chinese
Lew	95	Math
Lew	97	English

5,XML与表之间的转换

XML在SQL Server 2005/2008或者之后的版本得到了强化，也可以包含在我们的转换中，即将多列多行可以转换为一个XML列，或者非类型的XML即varchar/nvarchar。参考 http://hi.baidu.com/1987raymond/blog/item/5e08f3fcfde49a88b801a049.html

如下面的SQL语句：

select * from test4 for xml auto,type,root('Test')

结果为：

<Test>
  <test4 student="Lee" Chinese="70" Math="80" English="90" />
  <test4 student="Lew" Chinese="60" Math="95" English="97" />
</Test>

select * from test4 as t for xml auto,elements,root('Test')

结果为：

<Test>
  <t>
    <student>Lee</student>
    <Chinese>70</Chinese>
    <Math>80</Math>
    <English>90</English>
  </t>
  <t>
    <student>Lew</student>
    <Chinese>60</Chinese>
    <Math>95</Math>
    <English>97</English>
  </t>
</Test>