使用pivot wuery如何连接sql中的行 [英] using pivot wuery how to concate the the rows in sql
本文介绍了使用pivot wuery如何连接sql中的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择 * FROM Tb_SCh_TIme_Table P PIVOT(MAX(Faculty_Code) FOR 会话 IN ([ 1 ], [ 2 ],[ 3 ],[ 4 ])) AS PVT 其中 course = ' PH2' 订单 按课程;
来自我的上述查询输出,如下所示;
日期课程1 2 3 4
15/1/2013 AFF CM
15/1/2013 AFF NR
i想要以下输出如下;
date course 1 2 3 4
15/1/2013 AFF CM,NR
我该怎么办。
使用 PIVOT
查询我们可以连接行并放入一行。
如何使用我的上述查询id。
解决方案
以下是完整的查询依据我
SELECT *
FROM (
SELECT [日期],[课程],[会话],
SUBSTRING(( SELECT ' ,' + [Faculty_Code]
FROM [Tb_SCh_TIme_Table] dp2
WHERE dp2。[ 日期] = dp1。[日期] 和 dp2。[课程] = dp1。[课程] 和 dp2。[Session] = dp1。[Session]
FOR XML PATH(' ') ), 2 , 8000 ) AS [ Faculty_Code]
FROM [Tb_SCh_TIme_Table] dp1
GROUP BY [日期],[会话],[课程]
)P
PIVOT(
MAX([Faculty_Code])
FOR [Session] IN ([ 1 ],[ 2 ],[ 3 ],[< span class =code-digit> 4 ])
) AS PVT
WHERE [Course] = ' PH2'
select * FROM Tb_SCh_TIme_Table P PIVOT (MAX(Faculty_Code) FOR Session IN ([1],[2],[3] ,[4])) AS PVT where course = 'PH2' order by course;
from my above query output as follows;
date course 1 2 3 4
15/1/2013 AFF CM
15/1/2013 AFF NR
i want the below output as follows;
date course 1 2 3 4
15/1/2013 AFF CM,NR
how can i do.
using PIVOT
query we can concatenation the rows and put in a single row.
how can id using my above query.
解决方案
Here is the complete query according to me
SELECT * FROM ( SELECT [Date],[Course],[Session], SUBSTRING((SELECT ','+ [Faculty_Code] FROM [Tb_SCh_TIme_Table ] dp2 WHERE dp2.[Date] = dp1.[Date] and dp2.[Course]=dp1.[Course] and dp2.[Session]=dp1.[Session] FOR XML PATH('')), 2, 8000) AS [Faculty_Code] FROM [Tb_SCh_TIme_Table ] dp1 GROUP BY [Date],[Session],[Course] ) P PIVOT ( MAX([Faculty_Code]) FOR [Session] IN ([1],[2],[3] ,[4]) ) AS PVT WHERE [Course]='PH2'
这篇关于使用pivot wuery如何连接sql中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文