【转】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?')

执行语句后,数据表中的数据如下:

idc1c2c3
1I amsplit bythese three columns.
2Could youplease change me to bea 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)

执行语句后,数据表中的数据如下:

idstudentclassscore
1LeeChinese70
2LeeMath80
3LeeEnglish90
4LewChinese60
5LewMath95
6LewEnglish97

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

在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
studenChineseMathEnglish
Lee708090
Lew609597

在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
studenChineseMathEnglish
Lee708090
Lew609597

运行结果一致,而@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
studentscoreclass
Lee70Chinese
Lee80Math
Lee90English
Lew60Chinese
Lew95Math
Lew97English

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>



评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值