March 6, 2008 at 11:04 am
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!
March 6, 2008 at 11:14 am
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
*/
March 6, 2008 at 11:45 am
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
March 6, 2008 at 11:24 pm
Thanks guys, much appreciated! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply