combine multiple rows into one 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)

combine multiple rows into one row

Author

Topic

gnethery
Starting Member

1 Post

Posted - 2009-06-19 : 15:45:28

I could use help with the following script. The goal is to get all the Guideline_criteria in one row for each unique Account_Number and Assgn_Shift_Summary_ID combination.

Here is the data:


DECLARE @temp  TABLE (Account_Number CHAR(13), Assgn_Shift_Summary_ID char(10), Guideline_Criteria VARCHAR(255))
 
INSERT INTO @temp
SELECT 'BRK5016980228', '1009175' , 'CRT patient that remains on the unit.' UNION ALL
SELECT 'BRK5016980228', '1009175' , 'Epilepsy requiring monitoring. (2:1 ratio)' UNION ALL
SELECT 'BRK5017167938',  '1008775' , 'Dialysis or bedside pheresis– day of.' UNION ALL
SELECT 'BRK5017167938',  '1008775' , 'Moribund/DNRs requiring >30 minutes or with large family.' UNION ALL
SELECT 'BRK5016980228',  '1008775' , 'Physiological instability is LIFE-THREATENING (codes, status asthmaticus, status epilepticus).' UNION ALL
SELECT 'BRK5016980228',  '1008775' , 'Care off unit > 1 hour (e. g. surgery, transport)' UNION ALL
SELECT 'BRK5016980228',  '1009175' , 'Seizure patient actively seizing/status epileptus'
 
SELECT * FROM @temp

This returns:
Account_Number,Assgn_Shift_Summary_ID,Guideline_Criteria
BRK5016980228,1009175,CRT patient that remains on the unit.
BRK5016980228,1009175,Epilepsy requiring monitoring. (2:1 ratio)
BRK5017167938,1008775,Dialysis or bedside pheresis– day of.
BRK5017167938,1008775,Moribund/DNRs requiring >30 minutes or with large family.
BRK5016980228,1008775,Physiological instability is LIFE-THREATENING (codes, status asthmaticus, status epilepticus).
BRK5016980228,1008775,Care off unit > 1 hour (e. g. surgery, transport)
BRK5016980228,1009175,Seizure patient actively seizing/status epileptus

What I would like to have returned is:
Account_Number,Assgn_Shift_Summary_ID,Guideline_Criteria
BRK5016980228,1009175,CRT patient that remains on the unit. ; Epilepsy requiring monitoring. (2:1 ratio) ; Seizure patient actively seizing/status epileptus
BRK5017167938,1008775,Dialysis or bedside pheresis– day of. ; Moribund/DNRs requiring >30 minutes or with large family.
BRK5016980228,1008775,Physiological instability is LIFE-THREATENING (codes, status asthmaticus, status epilepticus). ; Care off unit > 1 hour (e. g. surgery, transport)

For each unique combination of Account_Number and Assgn_Shift_Summary_ID return all Guideline_criteria seperated by (space);(space).

Thanks in advance for any guidance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-20 : 02:44:53

use a UDF like below


CREATE FUNCTION ConcatValues
(@AccntNo varchar(20))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @RetVal Varchar(8000)
SELECT @RetVal=COALESCE(@RetVal,'') + Guideline_Criteria + ';'
FROM YourTable
WHERE Account_Number=@AccntNo

RETURN @RetVal
END
GO

then use it like this

SELECT DISTINCT Account_Number,Assgn_Shift_Summary_ID,dbo.ConcatValues(Account_Number) AS Guideline_Criteria
FROM YourTable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-20 : 03:28:27

also read this Rowset string concatenation: Which method is best ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources