SQL - Multiple text values in the same field

age · #1 (**permalink**) 08-05-08, 00:22

Hi

Is there any way you can concatenate multiple text values in the one field? The following query....

Code:

select distinct name.id, education.course
from name
inner join education on name.id = education.id

...returns a new record for each course the person has completed. Is there a way that all these values could be concatenated in one field?

Currently something like this is returned.

ID course
111 bachelor of arts
111 bachelor of law
111 master of arts

The following would be ideal.

ID course
111 bachelor of arts, bachelor of law, master of arts

Is this possible?

Thanks in advance for any help

r937 · #2 (**permalink**) 08-05-08, 00:37

not in ANSI SQL, no

it's trivially easy in MySQL, more difficult in other database systems

for example, in SQL Server, you'd write a user-defined function, such as this --
http://sqlblindman.googlepages.com/c...limitedstrings

age · #3 (**permalink**) 08-05-08, 01:00

Thanks for that

. I modified the code in that link to...

Code:

declare @DelimitedString varchar(500)

Select  @DelimitedString = isnull(@DelimitedString + ', ', '') + education.course
From    education

select distinct name.id, @DelimitedString
from name
inner join education on name.id = education.id

...it does concatenate multiple values in one field, however it includes every value contained in the course field on every record (until it runs over 500 chars

), while I only want the courses that an individual has completed. Should there be a group by in there somewhere?

gvee · #4 (**permalink**) 08-05-08, 04:09

Quote:

Originally Posted by age

I only want the courses that an individual has completed

WHERE clause

stolze · #5 (**permalink**) 08-05-08, 04:31

Quote:

Originally Posted by r937

not in ANSI SQL, no

Actually, you can do that quite easily with recursive SQL, which is standardized. It can typically be found under "bill of materials".

r937 · #6 (**permalink**) 08-05-08, 05:12

oh, that's right, Knut, recursive CTE

can you show an example please?

stolze · #7 (**permalink**) 08-05-08, 07:04

Sure. One example can be found here: http://publib.boulder.ibm.com/infoce.../r0000879.html Instead of multiplying quantities, you can use the CONCAT (or ||) operator to deal with strings.

Note that the SQL standard requires the additional RECURSIVE keyword, i.e. it would have to be "WITH RECURSIVE RPL ( ..."

Peter.Vanroose · #8 (**permalink**) 08-12-08, 17:43

Quote:

Originally Posted by r937

oh, that's right, Knut, recursive CTE

can you show an example please?

See e.g. the threads http://www.dbforums.com/ansi-sql/1612892-query-special-transpose-merge-data.html#post6248263, Concatenate column values from multiple rows and How to concat several lines into one
Note that you need to specify the concatenation order in some way or the other, e.g. through a third column (or in the example it's alphabetic on the second column).
(Aggregate SUM, COUNT etc. are order-indifferent, CONCAT isn't, that's why there is no aggregate CONCAT function.)

Internet Services

Web Development

Digital Marketing

Consumer Tech