QUERY FOR ROWS TO COLUMNS - SQL Server Forums

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 2008 Forums

Other SQL Server 2008 Topics

QUERY FOR ROWS TO COLUMNS

Author

Topic

kalinbac
Starting Member

6 Posts

Posted - 2011-06-15 : 04:27:49

hi, i have two columns called Profile and ProfileCommunication.

Profile Table
_ID _Name _Birthday _SSN
101 John 5/12/1970 23213323
103 Mary 6/3/1969 21323366
105 Frank 3/5/1980 5656779
ProfileCommunication Table
_ID _ProfileID _CommInfo _ComType
1 101 555-1434 Phone
2 101 john@xxxx.com Email
3 101 462-555-1243 mobile
4 103 mary@xx.com Email
5 103 555-7676 Phone
Here the problem comes...
I want my query to retrive John's or anybody's Communication informations and show them in a single row but not in one column. I want to see each comminfo in their own column like this

Name Phone Email Mobile
John 555-1434 john@xxx.com 462-555-1243
Mary 555-7676 mary@xx.com ----------

Any ideas? thanks for help

If there is nothing to do, there is nothing to do...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 05:33:40

select name = p._name ,
phone = max(case when pc._ComType = 'Phone then pc._CommInfo end) ,
...
from profile p
left join ProfileCommunication pc
on p._id = pc._ProfileID
group by p._Name

or you could use a pivot statement.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sanjib
Starting Member

2 Posts

Posted - 2011-06-15 : 11:31:34

select *
from dbo.records
where [Direct Phone]= 'NULL'

I used this query to show the records where direct phone is NULL

Sanjib
Starting Member

2 Posts

Posted - 2011-06-15 : 11:35:14

select *
from dbo.records
where [Direct Phone]= 'NULL'

I used this query to show the records where direct phone is NULL but i can't execute the result. can you help me?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-15 : 11:41:52

Please don't hijack a thread, post a new one with your question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources