grouping data by field value - 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

General SQL Server Forums

New to SQL Server Programming

grouping data by field value

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-01-27 : 14:24:42

Hi All.
I have select


select
Patient_Id
,User_Id
,Provider_Id
,LName
,ContactType
,Phone
from dbo.PatientProvider pa
Where ContactType in ('home', 'work', 'cell')

as a result I have
1 7 18 AAA Work (111) 111-1111
4 7 560 BBB Work 2-2222
4 7 560 BBB Cell (917) 123-4567

Is it possible to get result like
1 7 18 AAA Work (111) 111-1111
4 7 560 BBB Work 2-2222
4 7 560 Cell (917) 123-4567

If yes how it to do?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-27 : 14:47:55

[code]SELECT
Patient_Id,
USER_ID,
Provider_Id,
CASE WHEN RN = 1 THEN LName ELSE '' END AS LName,
ContactType,
Phone
FROM
(
SELECT
Patient_Id,
USER_ID,
Provider_Id,
LName,
ContactType,
Phone,
ROW_NUMBER() OVER (PARTITION BY Patient_id ORDER BY ContactType DESC) AS RN
FROM
dbo.PatientProvider pa
WHERE
ContactType IN ('home', 'work', 'cell')
)S
ORDER BY
ContactType DESC[/code]If you need to change the order by clause in the final select, change the order by clause in the row_number function to match.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources