首页运维零件 › 二、一个字段多条记录的拼接,PIVOT的一般语法是

二、一个字段多条记录的拼接,PIVOT的一般语法是

stuff((select ',' + ep_name from ep_detail where ep_classes =
a.ep_classes for xml path('')), 1, 1, '')

一、拼接多个字段的值

SELECThobby+','FROMstudent
WHEREsName=A.sName
FORXMLPATH

这句是把得到的内容以XML的形式显示。

 

SELECT'['+hName+']'FROM@hobbyFORXMLPATH

for xml
path有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。那么以一个实例为主.

 

图片 1

接下来我们来看应用FOR XML PATH的查询结果语句如下:

 

6、备注
如果结果值大于返回类型支持的最大值,则产生错误。

示例

  

图片 2

原先的表名为Table_A,实现代码如下:

 

一个整数值,指定删除和插入的开始位置。如果 start 或 length
为负,则返回空字符串。如果 start 比第一个
character_expression长,则返回空字符串。start 可以是 bigint 类型。

图片 3

如student表:

SELECT'{'+STR+'}','['+hName+']'FROM@hobbyFORXMLPATH

stuff:

②for xml path:

2、语法

2、参数
•param1:一个字符数据表达式。param1可以是常量、变量,也可以是字符列或二进制数据列。
•startIndex:一个整数值,指定删除和插入的开始位置。如果 startIndex或
length
为负,则返回空字符串。如果startIndex比param1长,则返回空字符串。startIndex可以是
bigint 类型。
•length:一个整数,指定要删除的字符数。如果 length
比param1长,则最多删除到param1 中的最后一个字符。length 可以是 bigint
类型。
•param2,返回类型。如果param1是受支持的字符数据类型,则返回字符数据。如果param1是一个受支持的
binary 数据类型,则返回二进制数据。

 

这时,我们的要求是查询学生表,显示所有学生的爱好的结果集,代码如下:

示例

图片 4

PIVOT(

类别 名称
AAA 企业1,企业2,企业3
BBB 企业4,企业5

解析:

二、PIVOT 行转列

由此可以看出 FOR XML PATH 可以将查询结果根据行输出成XML各式!

来源:

好的 FOR XML PATH就基本介绍到这里吧,更多关于FOR
XML的知识请查阅帮助文档!

图片 5

studentID

studentName

studentScore

01

Alice

90

02

Bill

95

03

Cindy

100

结果如下:图片 6

这句是把拼接的内容的第一个“,”去掉

①stuff:
1、作用
stuff(param1, startIndex, length, param2)
将param1中自startIndex(SQL中都是从1开始,而非0)起,删除length个字符,然后用param2替换删掉的字符。

create table employee(empid integer,fname nvarchar,lname nvarcharinsert into employeeselect 2021110,'小','张' insert into employeeselect 2021110,'小','李'  create table test(id integer,num integer) insert into testselect 2021110,2 insert into testselect 2,2  期望结果语句:select * from  employee2021110,'大','张'2021110,'大','李'2,'大','大'

一开始挺头疼的(会了的肯定没有这种感觉,不会那必须是头疼啊(*^__^*)
),从网上找了点资料,算是找到一种比较简单方便的方法吧,现在大体总结一下,供大家共同学习。

2、参数
param1
一个字符数据表达式。param1可以是常量、变量,也可以是字符列或二进制数据列。
startIndex
一个整数值,指定删除和插入的开始位置。如果 startIndex或 length
为负,则返回空字符串。如果startIndex比param1长,则返回空字符串。startIndex可以是
bigint 类型。
length 
一个整数,指定要删除的字符数。如果 length
比param1长,则最多删除到param1 中的最后一个字符。length 可以是 bigint
类型。

SQL语句实现:

select stu_name,stu_course from stu_courses for xml path;

3、返回类型
如果param1是受支持的字符数据类型,则返回字符数据。如果param1是一个受支持的
binary 数据类型,则返回二进制数据。
4、备注
如果结果值大于返回类型支持的最大值,则产生错误。 

下面是结果集

结果如下:

图片 7

FOR pivot_column --列名 (集合列转成行后的名字 例如:课程)

for xml path('')

结果:

PIVOT用于将列值旋转为列名,在SQL
Server2000可以用聚合函数配合CASE语句实现

假设有个表存放着学生的选课情况(stu_courses):

    for xml
path有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活存储过程来完成的工作。

select a.*,b.* FROM dbo.SQ_AJBL AS a
left join SQ_AJBLWorkFlow AS b on a.gID=b.gPID
where b.dCreateDate IN (select max(dCreateDate) from SQ_AJBLWorkFlow
GROUP BY gPID)

这里使用了SQL Server 2005版本以后加入的stuff以及for xml
path,先说下在上面这句sql中的作用,然后再详细的说明一下这两个的用法。

 

start

for xml path

二、一个字段多条记录的拼接

结果:

下面大家先看下示例代码:

select stuff((select '|'+studentName from student for xml path
('')),1,1,'')

以下示例在第一个字符串 abcdef 中删除从第 2
个位置开始的三个字符,然后在删除的起始位置插入第二个字符串,从而创建并返回一个字符串

stuff(param1, startIndex, length, param2)
将param1中自startIndex(SQL中都是从1开始,而非0)起,删除length个字符,然后用param2替换删掉的字符。

 

三、关于主从表关联取从表最大数据一条记录问题

昨天遇到一个SQL
Server的问题:需要写一个储存过程来处理几个表中的数据,最后问题出在我想将一个表的一个列的多行内容拼接成一行,比如表中有两列数据

结果:

图片 8

select 
    类别, 
    名称 = (
        stuff(
            (select ',' + 名称 from Table_A where 类别 = A.类别 for xml path('')),
            1,
            1,
            ''
        )
    ) 
from Table_A as A group by 类别

select studentID+‘-’+studentName+'-'+studentScore AS studentInfo from
student

那么其他类型的列怎么自定义?
没关系,我们将它们转换成字符串类型就行啦!例如:

select STUFF('abcdefg',1,0,'1234')    --结果为'1234abcdefg' 
select STUFF('abcdefg',1,1,'1234')    --结果为'1234bcdefg' 
select STUFF('abcdefg',2,1,'1234')    --结果为'a1234cdefg' 
select STUFF('abcdefg',2,2,'1234')    --结果为'a1234defg'

图片 9图片 10图片 11SELECTB.sName,LEFT(StuList,LEN-1)ashobbyFROM(
SELECTsName,
(SELECThobby+','FROMstudent
WHEREsName=A.sName
FORXMLPATHASStuList
FROMstudentA
GROUPBYsName
)B图片 12图片 13图片 14

1、作用

剩下的代码首先是将表分组,在执行FOR XML PATH
格式化,这时当还没有执行最外层的SELECT时查询出的结构为:

我想把这个表变成如下格式:

那么,如何改变XML行节点的名称呢?代码如下:

类别 名称
AAA 企业1
AAA 企业2
AAA 企业3
BBB 企业4
BBB 企业5

[爬山][游泳][美食]

SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO

table_source --数据源

分析:好的,那么我们来分析一下,首先看这句:

aijklmnef

三、

转载本站文章请注明出处:vns威尼斯城官网登入 http://www.tiec-ccpittj.com/?p=4318

上一篇:

下一篇:

相关文章