Subquery with multiple rows inside SELECT

• Psyonara

  SSC Enthusiast

  Points: 104

  More actions

  March 6, 2008 at 11:04 am

  #379067

  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!

• Kent Waldrop

  Hall of Fame

  Points: 3064

  More actions

  March 6, 2008 at 11:14 am

  #786232

  Another alternative might be something like:

  table (id int, profileName varchar(12))

  insert into @profiles

  select 1, 'Joe Soap' union all

  select 2, 'Jane Done' union all

  select 3, 'Ben Missing'

  declare @groups table

  (id int, groupName varchar(12), profileId int, orderCriteria int)

  insert into @groups

  select 1, 'Group 1-1', 1, 27 union all

  select 2, 'Group 1-2', 1, 14 union all

  select 3, 'Group 1-3', 1, 14 union all

  select 4, 'Group 1-4', 1, 17 union all

  select 5, 'Group 2-1', 2, 22

  select

  profileName,

  max( case when seq = 1 then rtrim(groupName) else '' end ) +

  max( case when seq = 2 then ', ' + rtrim(groupName) else '' end ) +

  max( case when seq = 3 then ', ' + rtrim(groupName) else '' end )

  as groupName

  from @profiles a

  outer apply

  ( select

  row_number() over( order by orderCriteria, id )

  as Seq,

  groupName

  from @groups p

  where a.id = p.profileId

  ) as b

  where ( seq <= 3 or seq is null )

  group by profileName

  /* -------- Sample Output: --------

  profileName groupName

  ------------ ----------------------------------------

  Ben Missing

  Jane Done Group 2-1

  Joe Soap Group 1-2, Group 1-3, Group 1-4

  */

• MeThinks

  SSC Eights!

  Points: 851

  More actions

  March 6, 2008 at 11:45 am

  #786260

  My first choice would be to create an UDF (I use S2K5) which returns a string with the first n group names, given an ID

  CREATE FUNCTION dbo.FirstNGroups (@ID INT, @N INT) -- @ID = ID of profile, @N max number of groups returned

  RETURNS VARCHAR(MAX)

  AS

  BEGIN

  DECLARE @RES VARCHAR(MAX) -- variable to store the result

  SELECT TOP (@N) -- select then first @n rows

  @RES =

  CASE WHEN @RES IS NULL THEN '' -- on first row, when @res is null, prepend an empty string

  ELSE @RES+',' -- on the next rows add in front of @res the value of @res, a comma and then Group Name

  END + GroupName

  FROM Groups

  WHERE ProfileID = @ID

  ORDER BY GroupName -- order in which the groups will appear in the result

  RETURN @RES

  END

  GO

  Then you would use

  SELECT Id, ProfileName, dbo.FirstNGroups(Id, 3) AS Groups -- first 3 group names

  FROM Profiles

• Psyonara

  SSC Enthusiast

  Points: 104

  More actions

  March 6, 2008 at 11:24 pm

  #786491

  Thanks guys, much appreciated!