Merging query rows into single row.

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

SQL Server 2000 Forums

Transact-SQL (2000)

Merging query rows into single row.

Author

Topic

smantz
Starting Member

6 Posts

Posted - 2012-08-01 : 17:57:28

I have a query, when run, gives me the following output:

ID Last First phone1 phone2 phone3 phone4
1 doe john 1 null null null
1 doe john 1 2 null null
1 doe john 1 null 3 null
1 doe john 1 null null 4
2 may sue x null null null
2 may sue x null y null
2 may sue x null null null
2 may sue x null null null
2 may sue x null null z

I was wondering if there is a way combine all the like id rows into just one row to end up with:

ID Last First phone1 phone2 phone3 phone4
1 doe john 1 2 3 4
2 may sue x null y z

Please keep in mind the original rows are the out put of a query.
Thanks,
SM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-01 : 18:38:33

can you show us your query ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 19:10:08

you've some additional columns in group by which is why values get split into rows

As per given output you should have only ID,last and first in group by

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smantz
Starting Member

6 Posts

Posted - 2012-08-01 : 19:28:09

I have the query at work so I will post it in the morning.

smantz
Starting Member

6 Posts

Posted - 2012-08-01 : 21:39:41

I found my query in another place so here it is:

Student extract
-- Rev 05-09-2012 by S Mantz -- Added case statements for additional phone contacts as demonstrated by Murphey2. Join statement added additional rows for various phone types. Need to clean up Null values in columns and combine rows into single row.

select
s.sid as ReferenceCode,
'Student' as ContactType,
s.fname as FirstName,
s.lname as LastName,
'Class of ' + s.gradyear as Status,
isnull (l.description,'') as Language,
s.sex as Gender,

case when hi.homephone1 is NULL then ''
when hi.homephone1=' ' then ''
else '(' + substring (hi.homephone1, 1,3) + ') ' + substring (hi.homephone1, 5,8)
end as PrimaryPhone,
'' as HomePhone,
CASE WHEN cn.PhoneTitle like 'Father% Work Phone' THEN Number
END Workphone,
CASE WHEN cn.PhoneTitle like 'Father% Cell Phone' THEN Number
END MobilePhone,
'' as HomePhoneAlt,
CASE WHEN cn.PhoneTitle like 'Mother% Work Phone' THEN Number
END WorkPhoneAlt,
CASE WHEN cn.PhoneTitle like 'Mother% Cell Phone' THEN Number
END MobilePhoneAlt,
isnull (s.email,'') as EmailAddress,
'' as EmailAddressAlt,
'' as SMSPhone,
'' as [Group],
'' as [Group],
'' as [Group],
'001' as Institution
from student s
left join person p on p.personid=s.persid
left join codes l on l.code=s.prilang and l.grpid='langua'
left join studenthomeinfo hi on hi.persid=s.persid
left join StudentContactNumbers cn on cn.sno=s.sno
where s.status='A'
order by s.lname, s.fname

This output creates several rows for each student id; a row for each phone type, whether it is null or has a value, for each student. The phone number for the "primary phone number" appears in every row. Some rows are all nulls (except primary phone... same number for every like id row)or if there is a phone number for the type, it appears. This is the first example I gave. What I need is my second example. Sorry if this looks awkward, but I used some pieces from another query to get started. Hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 22:01:29

[code]
select ReferenceCode,
ContactType,
FirstName,
LastName,
Status,
Language,
Gender,
MAX(PrimaryPhone) AS PrimaryPhone,
MAX(HomePhone) AS HomePhone,
MAX(Workphone) AS Workphone,
MAX(MobilePhone) AS MobilePhone,
MAX(WorkPhoneAlt) AS WorkPhoneAlt,
MAX(MobilePhoneAlt) AS MobilePhoneAlt,
MAX(EmailAddress) AS EmailAddress,
MAX(EmailAddressAlt) AS EmailAddressAlt,
SMSPhone,
[Group1],
[Group2],
[Group3],
Institution
from
(
select
s.sid as ReferenceCode,
'Student' as ContactType,
s.fname as FirstName,
s.lname as LastName,
'Class of ' + s.gradyear as Status,
isnull (l.description,'') as Language,
s.sex as Gender,

case when hi.homephone1 is NULL then ''
when hi.homephone1=' ' then ''
else '(' + substring (hi.homephone1, 1,3) + ') ' + substring (hi.homephone1, 5,8)
end as PrimaryPhone,
'' as HomePhone,
CASE WHEN cn.PhoneTitle like 'Father% Work Phone' THEN Number
END Workphone,
CASE WHEN cn.PhoneTitle like 'Father% Cell Phone' THEN Number
END MobilePhone,
'' as HomePhoneAlt,
CASE WHEN cn.PhoneTitle like 'Mother% Work Phone' THEN Number
END WorkPhoneAlt,
CASE WHEN cn.PhoneTitle like 'Mother% Cell Phone' THEN Number
END MobilePhoneAlt,
isnull (s.email,'') as EmailAddress,
'' as EmailAddressAlt,
'' as SMSPhone,
'' as [Group],
'' as [Group],
'' as [Group],
'001' as Institution
from student s
left join person p on p.personid=s.persid
left join codes l on l.code=s.prilang and l.grpid='langua'
left join studenthomeinfo hi on hi.persid=s.persid
left join StudentContactNumbers cn on cn.sno=s.sno
where s.status='A'
)t
GROUP BY ReferenceCode,
ContactType,
FirstName,
LastName,
Status,
Language,
Gender,
SMSPhone,
[Group1],
[Group2],
[Group3],
Institution
order by lname, fname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smantz
Starting Member

6 Posts

Posted - 2012-08-02 : 00:30:14

Visakh16 your great!
Had to make a couple of changes to clear up errors. Seems it doesn't like 3 undifferentiated "groups" (worked in the original query) so I added the 1,2 and 3 to Group, Group, and Group in the original section.
Also, the Order By had to have the alias' FirstName and LastName instead of lname and fname.
If you can think of why I have to have Group1 Group2 Group3 instead of just Group 3x let me know. This will get pasted into a spreadsheet to start with but I may be creating a DTS package for future scheduled runs, in which case I will probably need the "group" by 3x.
Thanks again for your great help and quick response.
-SM

smantz
Starting Member

6 Posts

Posted - 2012-08-02 : 14:36:23

I have fixed most everything but am curious; What is the letter t for at the end of the inner query?
I know you have to have it to make it work, but I am not sure why?
Thanks again,
-SM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 14:43:10

quote:
Originally posted by smantz

I have fixed most everything but am curious; What is the letter t for at the end of the inner query?
I know you have to have it to make it work, but I am not sure why?
Thanks again,
-SM

t is alias

ie short name you give for the derived table (ie table formed of inner query). it needs to have a name for reference

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smantz
Starting Member

6 Posts

Posted - 2012-08-02 : 16:35:55

Thanks for the info and all the great help.
SM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 16:55:32

u're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources