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

查看:49
本文介绍了如何使用towo查询make到单个子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的代码如下;

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆