CODEWITHSUNDEEP
sqlsql-server
user2007221
user2007221

Reputation: 37

Merge 2 Rows to 1 in SQL Server

I have the following rows on my query:

enter image description here

I would like to merge these rows to one row that will be presented as below:

enter image description here

Please note that my table holds more rows like this, so dynamic solution would be appreciated. In addition, There is several rows that will have NULL value in either Number 1 or Number 2 columns.

Thanks...

Upvotes: 1

Views: 521

Answers (3)

JonWay
JonWay

Reputation: 1735

THIS SHOULD WORK. THIS WILL ADDRESS THE ISSUE OF THE QUERY RETURNING MULTIPLE ROWS

DECLARE @MERGE TABLE (Category VARCHAR(10),    Category_2 VARCHAR(10),  
                      Number_1 INT,  Number_2 INT)
INSERT INTO @MERGE VALUES
('A', 'B', NULL,1),
('A', 'B', 5,NULL)

SELECT
 A.Category 
,A.Category_2   
,MAX(A.Number_1) AS Number_1        
,MAX(B.Number_2) AS Number_2

FROM  @MERGE A INNER JOIN @MERGE B
ON A.Category=B.Category
WHERE B.Number_2 IS NOT NULL
AND B.Number_2 IS NOT NULL
GROUP BY  A.Category ,A.Category_2

OUTPUT

Category    Category_2  Number_1    Number_2
  A            B           5          1

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35164

Depending on what's the key (let's assume Category), you can do grouping:

select Category, max(Category2) as Category2, max(number1) as number1, max(number2) as number2
from table
group by Category

If Category and Category2 together form the key, you could write:

select Category, Category2, max(number1) as number1, max(number2) as number2
from table
group by Category, Category2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

One method uses aggregation functions:

select category, category_2,
       max(number_1) as number_1, max(number_2) as number_2
from t
group by category, category_2;

You may also be able to fix the underlying query, so it does not return multiple rows. If that is of interest, then ask another question, with the query in the question.

Upvotes: 2

Related Questions