Select an aggregate list?
-
For some reason, I've never had to do this...
TablePerson contains this:
ID Name
== ====
1 Me
2 You
TableFriend contains this:
ID Friend
== =====
1 Bob
1 Bill
1 Harry
2 Bart
TablePref contains this:
ID Color
== ====
1 Blue
2 Blue
2 Green
Result set contains this:
ID Name Friends Colors == ==== ========= ==== 1 Me Bob,Bill,Harry Blue 2 You Bart Blue,Green Any takers?
Andrew Culver
-
Andrew,
You'll need to limit the outputs of the UDF's to make it fit your needs, but here's the whole ball of wax... explanation is in the comments. Includes the test setup (CAREFUL
, IT DROPS SOME TABLES AND FUNCTIONS AND I'M NOT RESPONSIBLE FOR YOUR DATA 
) and the two functions which are the stars of the show. I suppose the functions could be made into a single function that accepts table name and field name dynamically, but I didn't take the time to do that.--===== Set equivalent of NOLOCK on all tables to allow dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--===== Disable auto-display of rowcounts for appearance and speed
SET NOCOUNT ON
--===== If the tables already exist, drop them
IF OBJECT_ID('dbo.TablePerson') IS NOT NULL
DROP TABLE dbo.TablePerson
IF OBJECT_ID('dbo.TableFriend') IS NOT NULL
DROP TABLE dbo.TableFriend
IF OBJECT_ID('dbo.TablePref') IS NOT NULL
DROP TABLE dbo.TablePref
--===== Create the tables
CREATE TABLE dbo.TablePerson
(
ID INT PRIMARY KEY,
Name VARCHAR(10)
)
CREATE TABLE dbo.TableFriend
(
ID INT,
Friend VARCHAR(10),
CONSTRAINT PK_TableFriend_ID_Friend PRIMARY KEY (ID,Friend)
)
CREATE TABLE dbo.TablePref
(
ID INT,
Color VARCHAR(10),
CONSTRAINT PK_TablePref_ID_Color PRIMARY KEY (ID,Color)
)
--===== If functions already exist, drop them
IF OBJECT_ID('dbo.fnDelimFriend') IS NOT NULL
DROP FUNCTION dbo.fnDelimFriend
IF OBJECT_ID('dbo.fnDelimPref') IS NOT NULL
DROP FUNCTION dbo.fnDelimPref
--===== Create a couple of User Defined Functions to do the dirty work
GO
CREATE FUNCTION dbo.fnDelimFriend (@PersonID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @info VARCHAR(8000) --Working variable to build csv list
--===== Create csv list of friends for the given ID
SELECT @info = ISNULL(@Info + ',', '') + f.Friend
FROM dbo.TableFriend f
WHERE ID = @PersonID
ORDER BY f.Friend
RETURN (@Info)
END
GO
CREATE FUNCTION dbo.fnDelimPref (@PersonID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @info VARCHAR(8000) --Working variable to build csv list
--===== Create csv list of prefs for the given ID
SELECT @info = ISNULL(@Info + ',', '') + c.Color
FROM dbo.TablePref c
WHERE ID = @PersonID
ORDER BY c.Color
RETURN (@Info)
END
GO
--===== Populate the tables with test data from the posting
INSERT INTO dbo.TablePerson (ID,Name)
SELECT 1,'Me' UNION ALL
SELECT 2,'You'
INSERT INTO dbo.TableFriend (ID,Friend)
SELECT 1,'Bob' UNION ALL
SELECT 1,'Bill' UNION ALL
SELECT 1,'Harry' UNION ALL
SELECT 2,'Bart'
INSERT INTO dbo.TablePref (ID,Color)
SELECT 1,'Blue' UNION ALL
SELECT 2,'Blue' UNION ALL
SELECT 2,'Green'
--===== All set, make the requested output
SELECT p.ID,
p.Name,
dbo.fnDelimFriend(p.ID) AS Friends,
dbo.fnDelimPref(p.id) AS Colors
FROM TablePerson p
ORDER BY p.ID
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi, you cannot do it without the help of functions..
My answer:
create function return_friends (@id int )
returns varchar(100)
begin
declare @f varchar(100)
set @f=''
select @f= @f+friend+',' from TableFriends where id=@id
Return left(@f,len(@f)-1)
end
create function return_pref (@id int )
returns varchar(100)
begin
declare @C varchar(100)
set @C=''
select @C= @C+color+',' from TablePref where id=@id
Return left(@c,len(@c)-1)
end
Select id,name,dbo.return_friends(id),dbo.return_pref(id)
from TablePerson
-
You can do it without a UDF!
However, reading this nice little code code by SQL Server Adam Machanic http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true the question is rather, why?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply