CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Lajith
Lajith

Reputation: 1867

Merge the table based on date and condition

I have following table:

DROP TABLE IF EXISTS t

CREATE TABLE t
(
    id INT IDENTITY PRIMARY KEY,
    dt datetime,
    type int,
    grp int,
    typecol1 varchar(10),
    typecol2 varchar(10),
    typecol3 varchar(10),
    typecol4 varchar(10)
)

INSERT INTO t (dt,type,grp,typecol1,typecol2,typecol3,typecol4) 
VALUES
('2019-01-15',1,1,'A',null,null,null),
('2019-01-15',2,2,null,'B',null,null),
('2019-01-15',3,3,null,null,'C',null),
('2019-01-15',4,4,null,null,null,'D'),
('2019-02-15',1,1,'AA',null,null,null),
('2019-02-15',4,2,null,null,null,'DD'),
('2019-03-15',3,1,null,null,'CCC',null),
('2019-04-15',2,1,null,'BBBB',null,NULL);

In this table type will be 1,2,3,4.. here date and type both are composite key.

I need to merge the row based if same date exist to single row and merge based on only below condition

if same date & 
   type=1 then merge to typecol1
   type=2 then merge to typecol2
   type=3 then merge to typecol3
   type=4 then merge to typecol4

and grp col is based on running count of date.

Expected Out put :

Upvotes: 0

Views: 48

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try GROUP BY

FIDDLE DEMO

SELECT dt, MAX(typecol1) typecol1, MAX(typecol2) typecol2, MAX(typecol3) typecol3,
       MAX(typecol4) typecol4
FROM t
GROUP BY dt

Output

dt                  typecol1    typecol2    typecol3    typecol4
15/01/2019 00:00:00 A           B           C           D
15/02/2019 00:00:00 AA                                  DD
15/03/2019 00:00:00                         CCC 
15/04/2019 00:00:00             BBBB

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You just need grouping by ID with MAX() aggregation for rest of the columns :

SELECT dt,MAX(typecol1) as typecol1,
          MAX(typecol2) as typecol2,
          MAX(typecol3) as typecol3,
          MAX(typecol4) as typecol4
  FROM t
 GROUP BY dt

Demo

Upvotes: 1

Related Questions