Subquery with multiple rows inside SELECT

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)

Subquery with multiple rows inside SELECT

Author

Topic

whever
Starting Member

4 Posts

Posted - 2008-03-06 : 06:04:54

Hi there,
I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results.
Here is my basic structure:

Table: Profiles
Fields: Id, ProfileName

Table: Groups
Fields: Id, GroupName, ProfileId

I then need to return something like this:

ProfileName,Groups
"Joe Soap","Group1, Group2, Group3"

Does anyone know how this can be done?
Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 06:08:21

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-06 : 06:14:08

[code]DECLARE @Profiles table
(ID int,
ProfileName varchar(20)
)
DECLARE @Groups table
(ID int,
GroupName varchar(20),
ProfileId int)

INSERT INTO @Profiles VALUES (1,'Joe Soap')

INSERT INTO @GRoups VALUES(1,'Group1',1)
INSERT INTO @GRoups VALUES(2,'Group2',1)
INSERT INTO @GRoups VALUES(3,'Group3',1)

SELECT p.ProfileName,LEFT(gl.grouplist,LEN(gl.grouplist)-1) AS Groups
FROM @Profiles p
CROSS APPLY (SELECT GroupName + ',' AS [text()]
FROM @Groups
WHERE ProfileId=p.ID
FOR XML PATH(''))gl(grouplist)
output
---------------------
ProfileName Groups
-------------------- ----------------------
Joe Soap Group1,Group2,Group3

[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:25:46

Vishak, you are a CROSS APPLY specialist

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-06 : 08:30:44

quote:
Originally posted by madhivanan

Vishak, you are a CROSS APPLY specialist

Madhivanan

Failing to plan is Planning to fail

Thanks Madhi.

whever
Starting Member

4 Posts

Posted - 2008-03-07 : 01:28:22

Visak, I have no idea how your code does it, but it works! :) Thank you!

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-07 : 02:08:37

Hi vishak,
I dont know much about cross apply.But I think the above problem can be solved even without using cross apply.With this query I am getting the desired result.

select p.ProfileName,
(
select GroupName+ ',' from @Groups g where g.profileid=p.ID for xml path('')
)
as Groups from @Profiles p group by ID,ProfileName

Please let me know if I am wrong somewhere.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 03:12:09

quote:
Originally posted by ayamas

Hi vishak,
I dont know much about cross apply.But I think the above problem can be solved even without using cross apply.With this query I am getting the desired result.

select p.ProfileName,
(
select GroupName+ ',' from @Groups g where g.profileid=p.ID for xml path('')
)
as Groups from @Profiles p group by ID,ProfileName

Please let me know if I am wrong somewhere.

Yes can be done
He generalised it if you want to have concatenated values for more than one column in it's own

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources