Rows to single column without comma delimeter

[Corey B]

Maybe I need more coffee...but after searching this newsgroup and links I
cannot seem to to find a painless way to concatenate rows into a column
delimited by space(1) + '/' + space(1):

EXAMPLE: Accounting & Audit 8 / Other 3 / Tax 8

and update the "testEvents" table "FullCreditCat" column with that value.
This happens in the middle of the night so perfomance is a secondary
concern. Sorting by the "CreditCat" would be nice, but is not required.

Thanks for any insight,
Corey

Simplified DDL :

create table testEvents
(
IDYrCode char(10) NOT NULL,
CourseName char (20) NOT NULL,
FullCreditCat varchar(200),
primary key (IDYrCode)
)

create table testEventCredits
(
IDYrCode char(10) NOT NULL,
CreditCat varchar (20) NOT NULL
)
go

insert into testEvents values ('001', 'Some other course', ' ')
insert into testEvents values ('002', 'Some tax course', ' ')
insert into testEvents values ('003', 'Some CLE course', ' ')

insert into testEventCredits values ('001', 'Tax 8')
insert into testEventCredits values ('001', 'Other 3')
insert into testEventCredits values ('001', 'Accounting & Audit 8')
insert into testEventCredits values ('002', 'Tax 16')
insert into testEventCredits values ('003', 'Accounting & Audit 4')
insert into testEventCredits values ('003', 'Tax 5')
insert into testEventCredits values ('003', 'Other 3')
insert into testEventCredits values ('003', 'CLE 7')

[Anith Sen]

Pl. search the archives of this Newsgroup & you can find suggestions
for such problems posted very often. Here is one:

SELECT CourseName,
MAX(CASE n WHEN 1 THEN CreditCat ELSE '' END) +
MAX(CASE n WHEN 2 THEN ' / ' + CreditCat ELSE '' END) +
MAX(CASE n WHEN 3 THEN ' / ' + CreditCat ELSE '' END) +
MAX(CASE n WHEN 4 THEN ' / ' + CreditCat ELSE '' END)
FROM (
SELECT CourseName, CreditCat,
(SELECT COUNT(*) FROM testEventCredits t1
WHERE t1.IDYrCode = testEvents.IDYrCode
AND t1.CreditCat <= testEventCredits.CreditCat) n
FROM testEvents
INNER JOIN testEventCredits
ON testEvents.IDYrCode = testEventCredits.IDYrCode ) D
GROUP BY CourseName

--
- Anith
(Please respond only to newsgroups)

[Corey B]

Thanks for the response...I saw that solution but was trying to find a way
to avoid the harcoded count and update the parent table based upon the key
IDYrCode.

I guess I could just have "50" cases and thow it into a temp table but was
looking for something a little more elegant.
Corey

"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:uJ3wwG6yCHA.2288@TK2MSFTNGP09...

[Anith Sen]

Such problems are best handled at the client side using a programming
language or a report-writer. You can also use 3rd party tools like,
www.rac4sql.net & www.ag-software.com. If you are on SQL 2000, you
can try the following. However this is not a reliable or recommended
way of doing this.

CREATE FUNCTION ufn_Concat(@IDYrCode CHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @table TABLE(CreditCat VARCHAR(20))
DECLARE @Output VARCHAR(8000)
SET @Output = SPACE(0)
INSERT @table
SELECT CreditCat
FROM testEventCredits
WHERE IDYrCode = @IDYrCode
UPDATE @table
SET @Output = @Output + ' / ' + CreditCat
RETURN @Output
END
GO

Now, the query:

SELECT testEventCredits.IDYrCode,
dbo.ufn_Concat(testEventCredits.IDYrCode)

FROM testEvents
INNER JOIN testEventCredits
ON testEvents.IDYrCode = testEventCredits.IDYrCode

GROUP BY testEventCredits.IDYrCode