如何从选择查询中获取此输出

本文介绍了如何从选择查询中获取此输出的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

Select * from table1
select * from table2

table1

ID	StudentName    TotalMarks
21	Dipak	       100
22	Raunauque      100

table2

ID      SubName            Marks
21      Physics            60
21      Chemistry          40
22      Math               45
22      Computer Science   55

我想要这样的输出

I want output like this

ID     StudentName    Physics      Chemistry    Math    [Computer Science]
21     Dipak          60           40           N/A     N/A
21     Raunauque      N/A          N/A          55      45

我尝试了什么：

我正在尝试使用Pivot fn但不明白我是如何开始的。

What I have tried:

I am trying using Pivot fn but don't understand how do i Start.

推荐答案

WITH Dtl
AS
(
    SELECT *
        FROM table2
        PIVOT(
            SUM(Marks)
                FOR SubName
                IN([Physics],[Chemistry],[Math],[Computer Science])        -- [Physics],[Chemistry],[Math],[Computer Science]
        )AS DtlPivot
)
SELECT Student.*,
    COALESCE(Dtl.[Physics], 0) AS [Physics], -- assigning names for [Basic],[Hra],[TA]
    COALESCE(Dtl.[Hra], 0) AS [Chemistry],
    COALESCE(Dtl.[Math], 0) AS [Math],
	COALESCE(Dtl.[Computer Science], 0) AS [Computer Science]
    FROM table1 AS Student
    LEFT JOIN Dtl ON Student.ID = Dtl.ID

这篇关于如何从选择查询中获取此输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！