Help with query concatenate data from a second table

[johnstern]

I have table users

IDuser Name
1 John
2 Peter
3 JOsh

IDuser Skills
1 typist
1 acct
3 driver
3 Guard

I need to write a query that will show as follows

ID NAME SKILLS
1 John typist,acct
3 Josh Driver,Guard

 

[AlexCuse]

what database are you using?

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[johnstern]

sql server 2005

 

[AlexCuse]

You will need to use a user defined function. There is really not an ANSI solution to this problem.

Take a look at this example (run it in an empty database in case the table names match any of yours)

[COLOR=green]--set up sample data
[/color][COLOR=blue]create[/color] [COLOR=blue]table[/color] users (IDuser [COLOR=blue]int[/color], [COLOR=blue]Name[/color] [COLOR=blue]varchar[/color](10))
[COLOR=blue]create[/color] [COLOR=blue]table[/color] skills (IDuser [COLOR=blue]int[/color], Skill [COLOR=blue]varchar[/color](10))

[COLOR=blue]insert[/color] users
[COLOR=blue]select[/color] 1, [COLOR=red]'John'[/color]
union all [COLOR=blue]select[/color] 2, [COLOR=red]'Peter'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'Josh'[/color]

[COLOR=blue]insert[/color] skills
[COLOR=blue]select[/color] 1, [COLOR=red]'typist'[/color]
union all [COLOR=blue]select[/color] 1, [COLOR=red]'acct'[/color]
union all [COLOR=blue]select[/color] 2, [COLOR=red]'sniper'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'driver'[/color]
union all [COLOR=blue]select[/color] 3, [COLOR=red]'guard'[/color]
[COLOR=blue]go[/color]

[COLOR=green]--create user defined function
[/color][COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] concatSkills(@id [COLOR=blue]int[/color]) 

returns [COLOR=blue]varchar[/color](1000)

[COLOR=blue]as[/color]

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @skills1 [COLOR=blue]varchar[/color](1000)

[COLOR=blue]select[/color] @skills1 = [COLOR=#FF00FF]coalesce[/color](@skills1 + [COLOR=red]','[/color], [COLOR=red]''[/color]) + skill
	[COLOR=blue]from[/color] skills [COLOR=blue]where[/color] iduser = @id

[COLOR=blue]return[/color] @skills1

[COLOR=blue]end[/color]

[COLOR=blue]go[/color]


[COLOR=green]--use function to get your desired result
[/color][COLOR=blue]select[/color] IDuser
	, [COLOR=blue]Name[/color]
	, dbo.concatSkills(IDuser)
[COLOR=blue]from[/color] users

[COLOR=green]--clean up
[/color][COLOR=blue]drop[/color] [COLOR=blue]table[/color] users
[COLOR=blue]drop[/color] [COLOR=blue]table[/color] skills
[COLOR=blue]drop[/color] [COLOR=#FF00FF]function[/color] concatSkills

Hope it helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[johnstern]

thank you very much for taking the time to put this together

forgive my ignorance but what does it mean ANSI.

are you indication that is not possible to write a query like this ?

please see Pseudo code below --


select id, username, ( select @skills1 = coalesce(@skills1 + ',', '') + skill
from skills where iduser = @id) as skills
from users

I understand this does not work like that, but i was hoping to be able to do it without user define function so I would not have to write one for every single table I need this to happen - but if that is the case is ok, I want to make sure and understood and exhaust all the possibilities

thank you once again

 

[AlexCuse]

ANSI stands for american national standards institute (I think?) and it is the 'standard' for sql. Different databases have different levels of compliance with the standard.

The problem with doing this without the UDF is you need to basically use a cursor/loop to fill a temp table, then select everything that you want from there. You can only do this for 1 id value at a time, so unless it is encapsulated in a UDF it does not really fit in with a set-based query. If you wanted to spend a ton of time on this you could create a function that takes a bunch more parameters and builds a dynamic SQL statement, but I just don't think it would be worth it. The function as it is is pretty darn easy to create.

The best thing you could do is handle this in your presentation or reporting layer (IMO of course)

Hope this helps,

Alex


[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom