Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Displaying results of MAP
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 01/08/2008 :  13:41:40  Show Profile  Reply with Quote
I have two tables joined by a map. For example

[Ball, Frisbee, Kite]

[Red, Green, Blue]

[Ball:Green, Ball:Blue, Kite:Red]

I want to return a table that shows each toy and what colors it is available in.
Ball - Green, Blue
Kite - Red
Frisbee -

I have absolutely no idea how to do this even after googling all morning. Please help if you can. There must be a way to do this!

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 01/08/2008 :  15:52:08  Show Profile  Reply with Quote
Well if this is your first little project, you picked a little bit of a tricky one.
I made some assumptions as to table structure, datatypes, etc. If you can't apply this strategy to your actual tables then post the actual DDL of your tables (create table statements).

set nocount on

--setup the example
create table t1 (toy varchar(10))
create table t2 (color varchar(10))
create table m (toy varchar(10), color varchar(10))

insert t1 (toy) values ('Ball')
insert t1 (toy) values ('Frisbee')
insert t1 (toy) values ('Kite')
insert t2 (color) values ('Red')
insert t2 (color) values ('Green')
insert t2 (color) values ('Blue')
insert m (toy, color) values ('Ball', 'Green')
insert m (toy, color) values ('Ball', 'Blue')
insert m (toy, color) values ('Kite', 'Red')

--create a function to do the color concatenation
create function dbo.fnConcatColors(@toy varchar(10))
returns varchar(50)
	declare @out varchar(50)
	select @out = coalesce(@out + ', ' + color, color)
	from	m
	where	toy = @toy
	return @out

select	toy
		,dbo.fnConcatColors(t1.toy) [availableColors]
from	t1

--clean up
drop function dbo.fnConcatColors
drop table m
drop table t1
drop table t2

toy        availableColors
---------- --------------------------------------------------
Ball       Green, Blue
Frisbee    NULL
Kite       Red

Be One with the Optimizer
Go to Top of Page

Starting Member

3 Posts

Posted - 01/08/2008 :  21:54:35  Show Profile  Reply with Quote
Bingo, Bango, Boingo! This works a charm. For those that may stumble on this topic you can exactly copy TD's oh so helpful response to see how it works. And yes I am pretty new to SQL programming and yes this was definitely over my head. Other than personal help I think only reading a book was going to save me here.

If you are using Visual Studio - create the db tables as outlined above
1. Go to Server Explorer and connect to your DB
2. RMB Functions folder and choose New Function (type doesn't matter)
3. Delete everything in the function and cut/paste in

CREATE function dbo.fnConcatColors(@toy varchar(10))
RETURNS varchar(50)
    DECLARE @out varchar(50)
    SELECT @out = COALESCE(@out + ', ' + color, color)
    FROM   m
    WHERE   toy = @toy
    RETURN @out

4. RMB Stored Procedure folder and choose New. Delete all and cut/paste in:

SELECT    toy, dbo.fnConcatColors(t1.toy) AS Colors
FROM    t1

5. RMB the SQL block in your stored procedure and choose -> Design. Click execute and it will return exactly as you wanted.

6. Okay I know I am babying here, but I spent so much time on this today I have to share. If you had properly formed tables with ID columns for Toy and Color tables and the map referenced the ID's as opposed to directly referencing the colors you can use the following function and stored procedure to get your results.

CREATE PROCEDURE dbo.StoredProcedure1

    SELECT        toy, dbo.GetToyColors(toyid) AS Colors
    FROM            t1


    CREATE FUNCTION dbo.GetToyColors(@toy int)
    RETURNS varchar(50)
        Declare @out varchar(50)
        SELECT @out = COALESCE(@out + ', ' + CAST(color as varchar), color)
        FROM m INNER JOIN t2 ON m.colorid = t2.colorID
        WHERE toyid = @toy
        RETURN @out
Go to Top of Page

Starting Member

3 Posts

Posted - 01/08/2008 :  22:03:48  Show Profile  Reply with Quote
Also this person posted an alternate solution here.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 01/08/2008 :  23:39:49  Show Profile  Reply with Quote
Another 2005 specific soln:-
SELECT t.toy,LEFT(Colorlist.c,LEN(Colorlist.c)-1) AS 'AvailableColors'
FROM #t1 t
CROSS APPLY (SELECT m.color + ',' as [text()]
             FROM #m m
             WHERE m.toy=t.toy
             FOR XML PATH(''))Colorlist(c)

toy AvailableColors
---------- ----------------------------------------------------------
Ball Green,Blue
Frisbee NULL
Kite Red
Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 01/09/2008 :  01:40:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
More explanations


Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000