Help with query gets concatenate data from a secon

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 2005 Forums

Transact-SQL (2005)

Help with query gets concatenate data from a secon

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-08-24 : 14:39:38

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

with a skills column that has combine the values of the second table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 14:41:54

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-08-24 : 16:34:58

Peso

thank you

could please explain abit what is the difference between this two statements

SELECT DISTINCT s1.ID, STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID, STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-08-24 : 16:36:26

what does 100 PERCENT do ?

ranganath
Posting Yak Master

209 Posts

Posted - 2007-08-25 : 01:20:16

Hi,

Declare @Table1 Table (Id int, Username Varchar(100))
Insert into @Table1
Select 1, 'John' union all
Select 2, 'Peter'union all
Select 3, 'Josh'

Declare @Table2 Table (Id int, Userskills Varchar(100))
Insert into @Table2
Select 1, 'Typist' union all
Select 1, 'Acct'union all
Select 3, 'Driver' Union all
Select 3, 'Guard'

Declare @Temp Table (Id int, UserName Varchar(100), Userskills Varchar(100))
Insert into @Temp
Select T1.id, Username, Userskills
From @Table1 T1
Inner Join @Table2 T2 on T1.Id = T2.Id

Declare @Str Varchar(1000), @userskills Varchar(100)

Update @Temp
Set @userskills = Userskills = Case when @str = Id then @UserSkills + ', ' + Userskills Else Userskills End, @Str = Id

Select Id, UserName ,Max(Userskills) as 'Userskills' from @Temp Group By id,UserName

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-25 : 01:57:20

quote:
Originally posted by johnstern

what does 100 PERCENT do ?

It will select all rows

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-25 : 03:51:25

And it is REQUIRED for the ORDER BY to work when using ORDER BY in subqueries.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources