如何从选择查询中获取此输出 [英] How can i get this output from select query

问题描述

Select * from table1
select * from table2

table1

table1

ID	StudentName    TotalMarks
21	Dipak	       100
22	Raunauque      100

table2

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.

推荐答案

在查询下方尝试：

Try wit below query:

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

你需要Pivot，尝试探索它，非常简单。检查这些。



使用PIVOT和UNPIVOT [ ^ ]



在SQL查询中使用Pivot的简单方法 [ ^ ]

You need Pivot, Try exploring it, its pretty easy. Check these.

Using PIVOT and UNPIVOT[^]

Simple Way To Use Pivot In SQL Query[^]

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