How to make data of 2 rows into single row?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make data of 2 rows into single row??

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-01 : 07:51:39

Hello all,

I had a issue with this query.I am getting result set for Single Id into 2 rows. For example : user named : X have landline number and STD and Area code. For X ID =

result is coming like :


LandNo	LandAreaCodeLandCountryCode	MobileNo MobileCountyCode	
856856	5756	14	NULL	NULL
NULL	NULL	NULL	646474575	2

my output should come like :


LandNo	LandAreaCodeLandCountryCode	MobileNo MobileCountyCode
856856	5756	14 646474575	2

my query is like :

select case when isMobile=0 then Number end as LandNumber,
case when isMobile=0 then AreaCode end as LandAreaCode,
case when isMobile=0 then CountryCodeID end as LandCountryCode,
case when isMobile=1 then (Number) end as MobileNumber,
case when isMobile=1 then CountryCodeID end as MobileCountryCode
from Cust_ContactNumbers where Cust_Family_ID=150

how to get result set in one row

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-01 : 07:55:43

Try using an aggregate function (such as MAX) for each of the columns like shown below. If you expect a different row for each customer, you would also need to group by customerid or a similar column

SELECT MAX(CASE 
            WHEN isMobile = 0 THEN Number
       END)  AS LandNumber,
       MAX(CASE 
            WHEN isMobile = 0 THEN AreaCode
       END)  AS LandAreaCode,
       MAX(CASE 
            WHEN isMobile = 0 THEN CountryCodeID
       END)  AS LandCountryCode,
       MAX(CASE 
            WHEN isMobile = 1 THEN (Number)
       END)  AS MobileNumber,
       MAX(CASE 
            WHEN isMobile = 1 THEN CountryCodeID
       END)  AS MobileCountryCode
FROM   Cust_ContactNumbers
WHERE  Cust_Family_ID = 150

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-01 : 08:00:57

excellent james great one...it worked like a charm

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-01 : 08:24:02

Great! Glad it worked out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources