转自：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
运行结果：
| 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
| 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
运行结果：
| 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>