如何使用towo查询make到单个子查询 [英] how to use towo query make in to single sub query
本文介绍了如何使用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 usingTb_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屋!
查看全文