如何使用towo查询make到单个子查询 [英] how to use towo query make in to single sub query

问题描述

我的代码如下;

My Code as follows;

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,Date;

当我运行以上内容时查询输出如下;

when i run the above query output as follows;

Date       Course   1    2   3   4
15/1/2013   PH2                  NR
15/1/2013   PH2                  CM

select course,  stuff((select ',' + cast(SubQry.Faculty_Code as varchar(50)) from Tb_SCh_TIme_Table As SubQry where SubQry.course = MainQry.course for XML Path('')), 1, 1,'') as Faculty_Code
from Tb_SCh_TIme_Table as MainQry where Course='PH2'
Group by MainQry.course.

当我按如下方式运行上述查询输出时;

when i run the above query output as follows;

Course   Faculty_Code
 PH2      NR,CM

使用查询生成单个子查询i希望最终输出如下

using both query make into single sub query i want the final output to be as follows

 Date       Course   1    2   3    4
15/1/2013   PH2                   NR,CM

如何才能获得最终输出，如上所述。请帮助我。我想要答案。

how can i do to get the final output as i mentioned above.please help me. i want the answer.

推荐答案

而不是使用 Tb_SCh_TIme_Table 作为枢轴的来源使用你的子查询你的来源每个日期应该有1条记录，课程和（1-4）

Instead of using Tb_SCh_TIme_Table as your source for the pivot use your sub query so your source should have 1 record for each date,course and (1-4)

Date          Course         Num       Value
15/1/2013     PH2            1         NR,CM
15/1/2013     PH2            2         NR
15/1/2013     PH2            3         NR,CM,Oth

当你在 Num 列上有结果时。

据我所知您的以下查询是错误的。

According to me your following query is wrong.

select course,  stuff((select ',' + cast(SubQry.Faculty_Code as varchar(50)) from Tb_SCh_TIme_Table As SubQry where SubQry.course = MainQry.course for XML Path('')), 1, 1,'') as Faculty_Code
from Tb_SCh_TIme_Table as MainQry where Course='PH2'
Group by MainQry.course.

应该更正如下

It should be corrected as follows

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]

这是完整的查询根据我

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'

如果您需要更多详细信息，请查看并回复我。

Please check and get back to me if you need any more details.

这篇关于如何使用towo查询make到单个子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！