SQL Server 行列互相转换、合并

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

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

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

任务：将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)

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

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

在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

运行结果：

在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

运行结果一致，而@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

运行结果：

5,XML与表之间的转换

XML在SQL Server 2005/2008或者之后的版本得到了强化，也可以包含在我们的转换中，即将多列多行可以转换为一个XML列，或者非类型的XML即varchar/nvarchar。

如下面的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>