Multiple rows into 1 column - 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 2000 Forums

Transact-SQL (2000)

Multiple rows into 1 column

Author

Topic

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-05-17 : 06:41:29

I have the following SP;

SELECT dbo.tbl_ARS_Registers.RegisterID, sipr.dbo.ins_prs.prs_name, dbo.tbl_ARS_Registers.StartDate, dbo.tbl_ARS_Registers.EndDate
FROM   dbo.tbl_ARS_Registers
INNER JOIN dbo.tbl_ARS_Register_Lecturers ON dbo.tbl_ARS_Registers.RegisterID = dbo.tbl_ARS_Register_Lecturers.RegisterID
INNER JOIN sipr.dbo.ins_prs ON dbo.tbl_ARS_Register_Lecturers.LecturerID = sipr.dbo.ins_prs.prs_code
WHERE dbo.tbl_ARS_Registers.RegisterID = 17
GROUP BY dbo.tbl_ARS_Registers.RegisterID, sipr.dbo.ins_prs.prs_name, dbo.tbl_ARS_Registers.StartDate, dbo.tbl_ARS_Registers.EndDate

Which produces the results;

RegisterID prs_name StartDate EndDate
17 Joe Bloggs 2005-05-01 00:00:00 2005-05-31 00:00:00
17 Betty Bloggs 2005-05-01 00:00:00 2005-05-31 00:00:00
17 William Bloggs 2005-05-01 00:00:00 2005-05-31 00:00:00

However, I'd like the output to look like;

RegisterID Lecturers StartDate EndDate
17 Joe Bloggs, Betty Bloggs, William Bloggs 2005-05-01 00:00:00 2005-05-31 00:00:00

What's the easiest way to acheive this?

Thanks.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-17 : 07:45:10

Have a look at this article

[url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

Andy

Beauty is in the eyes of the beerholder

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-05-17 : 08:32:29

Thanks, I read a similar article somewhere else but I wasn't sure how to incorporate it in to my SP.

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-17 : 10:49:49

There is no direct function to concatinate particular columns data of multiple rows into a single row.

Use a LOOP/Cursor to fetch the data and check whether the Ids and dates are same then concatenate "prs_names" other wise skip to next record. You can implement this kind of stuff using stored procs OR User defined function is best.

With Regards
Sreenivas Reddy B

mparter
Yak Posting Veteran

86 Posts

Posted - 2005-05-17 : 17:46:05

For anybody that's interested, I solved this by creating a UDF as suggested, like so;

CREATE FUNCTION udf_ARS_ReturnLecturers
(
	@RegisterID int
)
RETURNS nvarchar (1000)
AS
BEGIN
DECLARE @Lecturers nvarchar (1000)
SET @Lecturers = ''
SELECT @Lecturers = @Lecturers + ', ' + prs_name FROM dbo.tbl_ARS_Register_Lecturers 
INNER JOIN sipr.dbo.ins_prs ON dbo.tbl_ARS_Register_Lecturers.LecturerID = sipr.dbo.ins_prs.prs_code
WHERE RegisterID = @RegisterID

SET @Lecturers = RIGHT(@Lecturers, (LEN(@Lecturers) - 2))

RETURN @Lecturers

END

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-18 : 11:16:26

Hi mparter

Your logic is working fine. But use distinct in query so that you can eliminate some duplicates.

With Regards
Sreenivas Reddy B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources