CODEWITHSUNDEEP
sqlsql-server
3daygg
3daygg

Reputation: 75

SQL Pivot and group by query

Can somebody help me with the following issue? Here's the situation , i have the following table :

signalName value date
Alarm 10 2020-01-01
Warning 7 2020-01-01
Check 4 2020-01-01
Alarm 1 2020-01-02
Warning 4 2020-01-02
Check 3 2020-01-02

Each day 3 records get added to my table , to register values for each signal , what i'd like to do here is to get the following table with a select statement:

valueAlarm valueWarning valueCheck date
10 7 4 2020-01-01
1 4 3 2020-01-02

So that i'm able to group by date and have for each record the 3 types of values for each signal , i was looking into the pivot examples documented here: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15 but can't get to have a working query.

Upvotes: 0

Views: 77

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Here I am sharing two way of solving your problem:

Schema:

 create table mytable (signalName varchar(20),  value   int, date date)
 insert into mytable values('Alarm',    10, '2020-01-01');
 insert into mytable values('Warning',  7,  '2020-01-01');
 insert into mytable values('Check',    4,  '2020-01-01');
 insert into mytable values('Alarm',    1,  '2020-01-02');
 insert into mytable values('Warning',  4,  '2020-01-02');
 insert into mytable values('Check',    3,  '2020-01-02');

Query with simple aggregation and group by:

 select max(case when signalname='Alarm' then value end)ValueAlarm,
  max(case when signalname='Warning' then value end )ValueWarning,
   max(case when signalname='Check' then value end )ValueCheck,
   date
   from mytable
   group by date

Output:

ValueAlarm ValueWarning ValueCheck date
10 7 4 2020-01-01
1 4 3 2020-01-02

Query with pivot:

 SELECT   
 Alarm ValueAlarm, Warning ValueWarning, [Check] ValueCheck,date
 FROM  
 (SELECT signalname,value,date
     FROM mytable) AS SourceTable  
 PIVOT  
 (  
 max(value)  
 FOR signalname IN ([Alarm], [Warning], [Check])  
 ) AS PivotTable;

Output:

ValueAlarm ValueWarning ValueCheck date
10 7 4 2020-01-01
1 4 3 2020-01-02

db<>fiddle here

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

the pivot syntax for your example should be something like this :

SELECT *
from(select * from table) src 
PIVOT (sum(value) FOR signalName in ([Alarm],[Warning],[Check]))  pvt

you can max() instead of sum() as well

Upvotes: 1

Related Questions