Search code examples
sql-serverselectpivotsql-server-2019

Select tables for display column as rows

I have follow statement:

select ID,
       Systemname,
       Mode,
       Evtdate,
       Sattus,
       Desc
From dbo.MainTasksStatus;

ID  SystemName Mode EvtDate Status  Desc
----------------------------------------------------------
125  Almo   Mode1   9/29/2023 9:11:00   TRUE    Complete All        
126  Almo   Mode2   9/28/2023 11:14:00  FALSE   Not Complete
127  Almo   Mode3   9/29/2023 10:28:00  TRUE    Complete Partial        
237  Dido   Mode2   9/27/2023 8:14:00   TRUE    Complete All    
238  Dido   Mode3   9/28/2023 12:48:00  TRUE    Complete Partial        
315  Faroon     Mode1   9/27/2023 9:11:00   FALSE   Not Complete
316  Faroon     Mode3   9/28/2023 15:22:00  TRUE    Complete All

As you can see, the records are based on the systemName. Systems are related to three Modes(Mode1,Mode2,Mode3) . some systems have all three modes and some have some of them.

I want select to produce the following output
enter image description here

In the search, I noticed that this is done with pivot, but I don't know exactly how to create this output.

Solution

  • You can do it with finding out what the names are and then left join a record from each mode to them.

    Test sample:

    create table something(name varchar(32), mode varchar(32));

insert into something(name, mode)
values
('a', 'Mode1'),
('b', 'Mode2'),
('c', 'Mode3'),
('d', 'Mode1'),
('d', 'Mode3'),
('e', 'Mode1'),
('e', 'Mode2'),
('e', 'Mode3');

    Query:

    select t.name, t1.Mode as Mode1, t2.Mode as Mode2, t3.Mode as Mode3
from (select distinct name from something) as t
left join something t1
on t.name = t1.name and t1.Mode = 'Mode1'
left join something t2
on t.name = t2.name and t2.Mode = 'Mode2'
left join something t3
on t.name = t3.name and t3.Mode = 'Mode3'

    Fiddle: http://sqlfiddle.com/#!18/2e215/13

    Result:

    enter image description here

    One can also run a single scan with conditional aggregation as @Thom A pointed out in the comment section. I have implemented and tested the idea, reaching this query:

    select name,
       max(
         case
             when Mode = 'Mode1' then Mode
         end
       ) as Mode1,
       max(
         case
             when Mode = 'Mode2' then Mode
         end
       ) as Mode2,
       max(
         case
             when Mode = 'Mode3' then Mode
         end
       ) as Mode3
from something
group by name;

    See: http://sqlfiddle.com/#!18/2e215/14