CODEWITHSUNDEEP
sqlsql-servergroup-by
calgara12
calgara12

Reputation: 117

SQL Combining multiple rows into one

I want to merge multiple rows into one, and only keep the values where the value is not NULL

Here is what i want to achieve:

I want from this

+----+-----------------+-----------------+-----------------+--------------------+
| ID | 1stNofification | 2ndNotification | 3rdNotification | NotificationNumber |
+----+-----------------+-----------------+-----------------+--------------------+
| 1  | 01.01.2019      | NULL            | NULL            | 1                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 1  | NULL            | 02.02.2019      | NULL            | 2                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 1  | NULL            | NULL            | 03.03.2019      | 3                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 2  | 06.01.2019      | NULL            | NULL            | 1                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 2  | NULL            | 09.02.2019      | NULL            | 2                  |
+----+-----------------+-----------------+-----------------+--------------------+
| 2  | NULL            | NULL            | 11.03.2019      | 3                  |
+----+-----------------+-----------------+-----------------+--------------------+

to this:

+----+-----------------+-----------------+-----------------+
| ID | 1stNofification | 2ndNotification | 3rdNotification |
+----+-----------------+-----------------+-----------------+
| 1  | 01.01.2019      | 02.02.2019      | 03.03.2019      |
+----+-----------------+-----------------+-----------------+
| 2  | 06.01.2019      | 09.02.2019      | 11.03.2019      |
+----+-----------------+-----------------+-----------------+

I tried something like:

SELECT 
ID, 
MAX(CASE WHEN a.NotificationNumber = 1 THEN 1stNotification END)1stNotification,
MAX(CASE WHEN a.NotificationNumber = 2 THEN 2ndNotification END)2ndNotification, 
MAX(CASE WHEN a.NotificationNumber = 3 THEN 3rdNotification END)3rdNotification

FROM Notifications

GROUP BY ID

But that did not give me my expected results unfortunately.

Would really appreciate if someone could help me out :)

Upvotes: 1

Views: 136

Answers (2)

DarkRob
DarkRob

Reputation: 3833

I think you need something like this...

 ; with cte as (
     select 1 as id, 'dd' as not1, null as not2, null as not3 , 1 as notifications
     union all 
     select 1, null, 'df', null  , 2 
     union all 
     select 1, null, null, 'vc', 3  
     union all 
     select 2, 'ws', null, null, 1  
     union all 
     select 2, null, 'xs', null, 2  
     union all 
     select 2, null, null, 'nm', 3  
 )
 , ct as (
    select id, coalesce(not1, not2, not3) as ol, notifications , 
    'notification' + cast(notifications as varchar(5)) as Col 
    from cte
 )
 select * from (
 select id, ol,  col from ct )
 as d 
 pivot (
 max(ol) for col in ( [notification1], [notification2], [notification3] )
 ) as P

Here as per my understanding your notification columns in result are actually notification number mention in rows.

Upvotes: 0

Shikhar Arora
Shikhar Arora

Reputation: 886

You just need to use max without any case

SELECT 
ID, 
MAX(1stNotification) AS 1stNotification,
MAX(2ndNotification) AS 2ndNotification, 
MAX(3rdNotification) AS 3rdNotification

FROM Notifications

GROUP BY  ID

Upvotes: 4

Related Questions