Query result to a single row per GUID

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

Transact-SQL (2005)

Query result to a single row per GUID

Author

Topic

gtaipalus
Starting Member

6 Posts

Posted - 2009-08-10 : 14:47:35

I have a SQL 2005 table that looks like this (2 columns, multiple rows per patient):
PatientID Allergy
---------- --------
1 Fish
1 Peanuts
2 Wheat
2 Dairy
3 Coconut

I need to create a query or temp table to display the records like this (2 columns and 1 row for each patient):
PatientID Allergy
---------- --------
1 Fish, Peanuts
2 Wheat, Dairy
3 Coconut

I found a similar posting here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127132
However this solution moves all PatientID's and Allergies to a single row and single column, instead of a separate row for each patient. Any idea's are appreciated.
Thank you!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-10 : 14:58:24

Dupe:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130939

Be One with the Optimizer
TG

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-10 : 15:06:30

Hi

Try this.. I am using SQL SERVER 2008. If you are using 2005 0r 2000 can you change the Insert statements...

so you can create the function and use it select clause. what i have wrote in below statements.



CREATE TABLE #temp
             (
                          PatientID INT ,
                          Allergy   VARCHAR(50)
             )
INSERT
INTO   #temp VALUES
       (
              1,
              'Fish'
       )
       ,
       (
              1,
              'Peanuts'
       )
       ,
       (
              2,
              'Wheat'
       )
       ,
       (
              2,
              'Dairy'
       )
       ,
       (
              3,
              'Coconut'
       )
SELECT *
FROM   #temp

DECLARE @COLUMN_ATTRIBUTES VARCHAR(8000)

SELECT @COLUMN_ATTRIBUTES=ISNULL(@COLUMN_ATTRIBUTES+',', '')+Allergy
FROM   dbo.#temp
WHERE  PatientID= 1

SELECT @COLUMN_ATTRIBUTES

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

gtaipalus
Starting Member

6 Posts

Posted - 2009-08-10 : 15:20:24

Thank you for the suggestion. I tried it, however I really need to end up with a row for each PatientID. I am getting allergies for all patients on a single row.

GT

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-10 : 15:27:35

HI gtaipalus

You can create function. there u can pass PatientID it will return concatenated names...

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources