Multiple rows into 1 column

Question

Post reply

Multiple rows into 1 column

Mark Parter-146528

Grasshopper

Points: 13
More actions
May 17, 2005 at 7:43 am

#69082

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?
I've already read the article http://www.sqlservercentral.com/faq/viewfaqanswer.asp?faqid=206 but I wasn't sure how to incorporate that into the SP shown above.
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Check this thread out and see if it helps.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=182229

This might be overkill. I over-interpretted what you wanted. I think Carl gave you the right answer.

I wasn't born stupid - I had to study.

Carl Farmer SSCrazy Points: 2197 More actions · Answer 2

If I understand this right, all you want to do is concatemate the rows into one? If this is the case, leave everything after the SELECT, and recode your SELECT like this:

SELECT dbo.tbl_ARS_Registers.RegisterID + ' ' + sipr.dbo.ins_prs.prs_name + ', ' + dbo.tbl_ARS_Registers.StartDate + ' ' + dbo.tbl_ARS_Registers.EndDate

Mark Parter-146528 Grasshopper Points: 13 More actions · Answer 3

Close, the output I'm after hasn't come out too great on screen. I want to concatenate all the prs_name fields into one field called Lecturers. I'll do it as a table;

RegisterID

prs_name

StartDate

EndDate

17

Joe Bloggs

2005-05-01

2005-05-31

17

Betty Bloggs

2005-05-01

2005-05-31

17

William Bloggs

2005-05-01

2005-05-31

And the output I want is;

RegisterID

Lecturers

StartDate

EndDate

17

Joe Bloggs, Betty Bloggs, William Bloggs

2005-05-01

2005-05-31

ron k SSCertifiable Points: 6103 More actions · Answer 4

Try creating a udf from the sample code below and use the returned list as your Lecturers column:

declare @csv varchar(1000)

select @csv = isnull(@csv + ', ', '') + prs_name

from Your_table

Where .....

Return @csv

Mark Parter-146528 Grasshopper Points: 13 More actions · Answer 5

For anybody that's interested, I solved the problem 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

ron k SSCertifiable Points: 6103 More actions · Answer 6

Nice to get some feedback. Just in case you were interested

select @csv = isnull(@csv + ', ', '') + prs_name

will eliminate the need for

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

Mark Parter-146528 Grasshopper Points: 13 More actions · Answer 7

I'm aware of how infuriating it can be when you find someone experiencing the same difficulty as yourself only to find that they never posted the outcome or solution in their post, so I thought I'd share mine for others.

Thanks for the tip.