CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bysql-server-2014
Parsu
Parsu

Reputation: 83

Combine multiple rows into one single

I have something like this in a table

COUNTRY SCOPE1   SCOPE2 SCOPE3  SCOPE4  SCOPE5
UK          Y    NULL   NULL    NULL    NULL
UK      NULL          Y NULL    NULL    NULL
UK      NULL     NULL      Y    NULL    NULL

I need output as single Row 

COUNTRY SCOPE1  SCOPE2  SCOPE3  SCOPE4  SCOPE5
UK          Y        Y       Y  NULL    NULL

Can anybody help on this in MS SQL Server 2014?

Upvotes: 2

Views: 62

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

You can use aggregate function max or min both will work for your data set

SELECT COUNTRY ,
        MAX(SCOPE1) as  SCOPE1,
        MAX(SCOPE2) as  SCOPE2,
        MAX(SCOPE3) as  SCOPE3,
        MAX(SCOPE4) as  SCOPE4,
        MAX(SCOPE5) as  SCOPE5
FROM yourtable
GROUP BY COUNTRY

http://sqlfiddle.com/#!18/3960c/1

COUNTRY     SCOPE1  SCOPE2  SCOPE3  SCOPE4  SCOPE5
UK           Y        Y       Y     (null)  (null)

or

SELECT COUNTRY ,
        min(SCOPE1) as  SCOPE1,
        min(SCOPE2) as  SCOPE2,
        min(SCOPE3) as  SCOPE3,
        min(SCOPE4) as  SCOPE4,
        min(SCOPE5) as  SCOPE5
FROM yourtable
GROUP BY COUNTRY

http://sqlfiddle.com/#!18/3960c/2

COUNTRY     SCOPE1  SCOPE2  SCOPE3  SCOPE4  SCOPE5
UK            Y      Y         Y    (null)  (null)

Upvotes: 2

D-Shih
D-Shih

Reputation: 46219

You can try to use MAX function.

CREATE TABLE T(
    COUNTRY VARCHAR(50),
    SCOPE1 VARCHAR(3),
   SCOPE2 VARCHAR(3),
   SCOPE3 VARCHAR(3),
   SCOPE4 VARCHAR(3),
   SCOPE5 VARCHAR(3)
);


INSERT INTO T VALUES ('UK','Y',NULL,NULL,NULL,NULL);
INSERT INTO T VALUES ('UK',NULL,'Y',NULL,NULL,NULL);
INSERT INTO T VALUES ('UK',NULL,NULL,'Y',NULL,NULL);

Query 1:

SELECT COUNTRY ,
        MAX(SCOPE1) AS SCOPE1,
        MAX(SCOPE2) AS SCOPE2,
        MAX(SCOPE3) AS SCOPE3,
        MAX(SCOPE4) AS SCOPE4,
        MAX(SCOPE5) AS SCOPE5
FROM T
GROUP BY COUNTRY

Results:

| COUNTRY | SCOPE1 | SCOPE2 | SCOPE3 | SCOPE4 | SCOPE5 |
|---------|--------|--------|--------|--------|--------|
|      UK |      Y |      Y |      Y | (null) | (null) |

Upvotes: 5

Related Questions