CODEWITHSUNDEEP
sql-servert-sqlmerge
Adi
Adi

Reputation: 19

merger multiple row to one in sql server

I have a table as below,

Date                 Type     ClassA   ClassB
--------------------------------------------------
02/05/2015             A        1        
04/05/2015             B        2        - 
04/05/2015             B        -        3  
05/05/2015             A        4        - 
05/05/2015             B        5        -

I am trying to merge the rows as below(Required Output)

Date                 Type     ClassA   ClassB
--------------------------------------------------
02/05/2015             A        1        -
04/05/2015             B        2        3 
05/05/2015             A        4        - 
05/05/2015             B        5        -

In the above example, when 'Date' and 'Type' matches in 2 separate rows, I should merge the other column values to become a single row

Upvotes: 1

Views: 42

Answers (2)

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT date, type, SUM(classA), SUM(classB)
FROM yourtable
GROUP BY date, type

Upvotes: 0

Chanukya
Chanukya

Reputation: 5883

CREATE TABLE #Table1
    ([Date] datetime, [Type] varchar(1), [ClassA] INT, [ClassB] INT)
;

INSERT INTO #Table1
    ([Date], [Type], [ClassA], [ClassB])
VALUES
    ('2015-02-05 00:00:00', 'A', '1', NULL),
    ('2015-04-05 00:00:00', 'B', '2', NULL),
    ('2015-04-05 00:00:00', 'B', NULL, '3'),
    ('2015-05-05 00:00:00', 'A', '4', NULL),
    ('2015-05-05 00:00:00', 'B', '5', NULL)
;

SELECT [Date] ,[Type], MAX(CLASSA) CLASSA,  MAX(ClassB)ClassB FROM #Table1
GROUP BY [Date] ,[Type]

Upvotes: 2

Related Questions