By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .

 

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

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

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

id
student
class
score
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
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>

 

info Last modified by Raymond at 6 years ago * This page is subject to Site terms.

More from Kontext

visibility 13662
thumb_up 0
access_time 6 years ago

SQL Server Compact 4.0 (CE 4.0) is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, Web clients and others. In one of my projects, I used it as the database for logging errors, which assumes the errors will onl...

open_in_new View open_in_new SQL Server

visibility 684
thumb_up 0
access_time 6 years ago

SQL Server provides a batch of great features to build robust, high-performance and scalable data solutions. ...

open_in_new View open_in_new SQL Server

visibility 1145
thumb_up 0
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario In the previous articles of this serial, I’ve introduced how to use SQL Server as database for online transaction proces...

open_in_new View open_in_new SQL Server

visibility 4123
thumb_up 1
access_time 6 years ago

Serial: An Introduction to SQL Server Features Case Scenario For this ETL project, the requirements are listed below: Sales data will be pushed to specified shared fold...

open_in_new View open_in_new SQL Server

info About author

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward
Kontext Column

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.

Learn more arrow_forward
info Follow us on Twitter to get the latest article updates. Follow us