使用信息单元格而不是列标题

Question

我在报告信息和构建视图时遇到问题 我的表信息如下

数据库

表斯图

---------------------------------------------------
----------------------< Stu >----------------------
---------------------------------------------------
| id | name  | age|id_Orientation | id_StudyStatus|
---------------------------------------------------
| 1  |  John | 24 |       1       |       1       |
| 2  |  Sara | 23 |       1       |       2       |
| 3  |  Mary | 26 |       1       |       3       |
| 4  |  Jax  | 25 |       2       |       1       |
| 5  | izable| 22 |       2       |       2       |
| 6  |  Tari | 23 |       2       |       3       |
| 7  |  Kap  | 26 |       3       |       1       |
| 8  |  Lio  | 25 |       3       |       2       |
| 9  |  Soti | 22 |       3       |       3       |
---------------------------------------------------

表 学习状态

-----------------
--<StudyStatus>--
-----------------
| id |   name   |
-----------------
| 1  | Studying |
| 2  |  Cancel  |
| 3  |laying off|
-----------------

表方向

-------------------
---<Orientation>---
-------------------
| id |   name     |
-------------------
| 1  |     IT     |
| 2  |Construction|
| 3  |   Medical  |
-------------------

[我最终想要的输出]

----------------------------------------------------------
----------------------< Stu >------------------------------
-----------------------------------------------------------
| id |    name    |  All  | Studying | Cancel | layingOff |
---------------------------------------------------
| 1  |     IT     |   3   |     1    |    1   |     1     |
| 2  |Construction|   3   |     1    |    1   |     1     |
| 3  |   Medical  |   3   |     1    |    1   |     1     |
-----------------------------------------------------------

通过什么查询？

请帮忙。 谢谢

Answer 1

试试吧。 但首先从 StudyStatus 的名称中删除左侧和右侧的空白区域。

select isnull([Studying],0)+isnull([Cancel],0)+isnull([Laying off],0)   [All],  Orientation_Name,[Studying],[Cancel],[Laying off] 
from
(
select s.id,o.Name Orientation_Name ,ss.Name  StudyStatus_Name
from Stu s inner join StudyStatus ss  on s.ID_StudyStatus=ss.ID inner join Orientation O on o.Id=s.ID_Orientation)as st
pivot
(
count(id)
FOR StudyStatus_Name   IN ([Studying],[Cancel],[laying off])
) as PV

Answer 2

你可以选择条件和并获得结果。

SELECT o.name,   
COUNT(*) AS All, 
SUM(CASE WHEN ss.Name = 'Studying' THEN 1 ELSE 0 END) AS Studying,   
SUM(CASE WHEN ss.Name = 'Cancel' THEN 1 ELSE 0 END) AS cancel,
SUM(CASE WHEN ss.Name = 'Laying Off' THEN 1 ELSE 0 END) AS LayingOff
FROM Orientation as o
inner join Stu as s
on s.id_orientation = s.id
inner join StudyStatus AS ss
on ss.id = s.id_StudyStatus
GROUP BY o.name

Answer 3

select isnull([Studying], 0) + isnull([Cancel], 0) + isnull([laying off], 0) [All],
Orientation_Name,
[Studying],
[Cancel],
[laying off]
from (
select s.id,
o.name Orientation_Name,
ss.name StudyStatus_Name
from Stu s
inner join StudyStatus ss on ss.id = s.id_StudyStatus
inner join Orientation o on o.id = s.id_Orientation
) as st pivot (
count(id) FOR StudyStatus_Name IN ([Studying], [Cancel], [laying off])
) as PV

注意大写和小写字母 移动内部连接

感谢@LiaqatKundi和@DaleK