return a single record from multiple entries

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)

return a single record from multiple entries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-06 : 08:15:53

Gani writes "Given:
SQL Server 7 on Windows 2000 server
table Person with column UserID (int) as unique
table Comments with column UserID (int), CommentText, CommentType (pk is autogenerated int) and comments of any given CommentType are limited to one per userid

I would like to return a recordset with:
UserID, PhoneComment (= commenttext if a record exists for this userid where CommentType = 'TXTPH' else null) , SalaryComment (same as previous field exc. CommentType = 'TXTSL')

I would like a single record for the person. Writing it down, I see it will likely be some kind of IIF, but I am NOT getting there.

I did look at the FAQ and peruse the queries topics, but am still stumped.

Thank you for your time in looking at this question.

Gani"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-01-06 : 08:33:07

Cross-tab using CASE statements:


SELECT	a.UserID
	, max(CASE WHEN b.CommentType = 'TXTPH' THEN b.CommentText ELSE NULL END) as 'PhoneComment'
	, max(CASE WHEN b.CommentType = 'TXTSL' THEN b.CommentText ELSE NULL END) as 'SalaryComment'
FROM	Person a LEFT JOIN Comments b ON a.UserID = b.UserID
Group by a.UserID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources