Use information cells instead of column heads

Question

I had trouble reporting information and building View My table information is as follows

DataBase

Table Stu

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

Table StudyStatus

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

Table Orientation

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

[The output I finally want]

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

By What Query ?

Please Help ... ! Thanks

Questions are better when they don't use images but instead use formatted text. Not only are they easier to read, but people can copy and paste the query and data out to try and assist you. — Dale K, Jun 18 '20 at 05:35

score 0 · Answer 1 · edited Jun 18 '20 at 04:41

0

Try it. but first remove the Blank Space of Left & Right Side from the Name of 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

edited Jun 18 '20 at 04:41

Dale K

25,246
15
42
71

answered Jun 18 '20 at 04:29

Liaqat Kundi

71
6

Which of your 2 answers actually answers the question? Please remove the one which doesn't. – Dale K Jun 18 '20 at 04:43
This one is Actual – Liaqat Kundi Jun 18 '20 at 04:47
So please remove your other answers. – Dale K Jun 18 '20 at 04:47
I deleted it but it didn't work. Can you give me a clearer picture or code that I can better understand? Because I said I was new to SQL. – Mahdi_Ush Jun 18 '20 at 05:06

score 0 · Answer 2 · answered Jun 18 '20 at 07:18

You can go for conditional sum and get the results.

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

score 0 · Accepted Answer · answered Jun 18 '20 at 07:37

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

Pay attention to uppercase and lowercase letters Move Inner Join

Thanks to @LiaqatKundi and @DaleK

Use information cells instead of column heads

DataBase

[The output I finally want]

3 Answers3