SQL查询多条记录拼接成一条记录

发布网友发布时间：2022-04-09 04:42

共3个回答

懂视网时间：2022-04-09 09:03

1 select PERSON_NAME from S_PROJECT_MEMBER where PROJECT_ID = ‘2c9081473e2f8bda013e3028e4700049‘;

技术分享

先直接上代码吧！-------------------------有问题的，麻烦指出--------------------------------------------------------

select 
 CAST(stuff((
 select distinct ‘,‘+PERSON_NAME 
 from S_PROJECT_MEMBER 
 where PROJECT_ID = ‘2c9081473e2f8bda013e3028e4700049‘ for xml path(‘‘)),1,1,‘‘) AS varchar(1000)
 ) 
 AS PERSON_NAMES 
from S_PROJECT_MEMBER 
where PROJECT_ID = ‘2c9081473e2f8bda013e3028e4700049‘ GROUP BY PROJECT_ID;

这里用的知识点有：

for xml path() 函数：

--这里用来做字符串拼接

CAST()函数:

--CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型
cast( "abcdefg" as varchar(2000));

STUFF()函数：

--STUFF()函数用于删除指定长度的字符，并可以在制定的起点处插入另一组字符。

--select stuff(列名,开始位置,长度,替代字符串)

DISTINCT 关键字：

--这里可以理解为去重，具体可以百度

AS 关键字：

--取别名

GROP BY ：

--用于分组

下面通过具体的例子来说明吧：来自于：http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

create table hobby(
hobbyId varchar(32),
hName varchar(32)
)

insert into hobby values(‘1‘,‘爬山‘);
insert into hobby values(‘2‘,‘游泳‘);
insert into hobby values(‘3‘,‘爬山‘);

--将查询结果根据行输出成XML格式
SELECT * FROM hobby FOR XML PATH;

--改变XML行节点的名称
SELECT * FROM hobby FOR XML PATH(‘MyHobby‘);

--改变XML列节点的名称
SELECT hobbyID as ‘MyCode‘,hName as ‘MyName‘ FROM hobby FOR XML PATH(‘MyHobby‘);

--构建我们喜欢的输出方式
SELECT ‘[ ‘+hName+‘ ]‘ FROM hobby FOR XML PATH(‘‘);

现在我对例子进行应用：

--应用
select distinct ‘,‘+hobbyId from hobby for xml path(‘‘);

-- for xml path(‘‘) 解决连接问题
select hName,(select distinct ‘+‘+hobbyId from hobby h where h.hName = hName for xml path(‘‘)) AS A 
from hobby 
group by hName;

-- stuff(String,1,1,‘‘) 去除开始的连接符 +1+2+3 --> 1+2+3
select hName,stuff((select distinct ‘+‘+hobbyId from hobby for xml path(‘‘)),1,1,‘‘) AS A 
from hobby 
group by hName;

--需要查询结果
‘爬山‘ ‘1‘+‘3‘
‘游泳‘ ‘2‘

--方法一
select hName,stuff((select distinct ‘+‘+hobbyId from hobby where hName = B.hName for xml path(‘‘)),1,1,‘‘) AS A 
from hobby B 
group by hName;

--方法二
SELECT B.hName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT hName,
(SELECT hobbyId+‘,‘ FROM hobby 
 WHERE hName=A.hName 
 FOR XML PATH(‘‘)) AS StuList
FROM hobby A 
GROUP BY hName
) B 
结果：

第一次写笔记。。。。。2016.06.25

SQL基础知识--多行查询结果拼接

标签：

热心网友时间：2022-04-09 06:11

select
departdate --发车时间

,sum(num) --售票张数

,(select sum(num) from table where departdate=t.departdate and stationlvl=N'一级站') --一级站

,(select sum(num) from table where departdate=t.departdate and stationlvl=N'二级站') --二级站
,(select sum(num) from table where departdate=t.departdate and stationlvl=N'*站') --*站

from table t
group by departdate

热心网友时间：2022-04-09 07:29

sqlserver写法，为了验证结果，在你提供数据基础上加了条数据，具体如下

创建表及数据

create table test
(departdate varchar(10),
stationlvl varchar(10),
num int)

insert into test values ('2014-07-28','一级站',200)
insert into test values ('2014-07-28','二级站',100)
insert into test values ('2014-07-28','*站',50)
insert into test values ('2014-07-29','一级站',300)
insert into test values ('2014-07-29','二级站',200)
insert into test values ('2014-07-29','*站',100)
insert into test values ('2014-07-30','*站',90)--这条为新增

执行

declare @sql varchar(4000)
set @sql = 'select [departdate]'
select @sql = @sql + ',sum(isnull(case [stationlvl] when '''+[stationlvl]+''' then [num] end,0)) as
['+[stationlvl]+']'
from (select distinct [stationlvl] from [test]) as a
select @sql = @sql+' from [test] group by [departdate]'
exec(@sql)

结果

SQL查询 多条记录拼接成一条记录

SQL查询多条记录拼接成一条记录