使用pivot wuery如何连接sql中的行 [英] using pivot wuery how to concate the the rows in 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屋！