SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

mrtsherman
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

Table1
[Ball, Frisbee, Kite]

Table2
[Red, Green, Blue]

Map
[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!

TG
Flowing Fount of Yak Knowledge

USA
5469 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
go
create function dbo.fnConcatColors(@toy varchar(10))
returns varchar(50)
as
begin
	declare @out varchar(50)
	select @out = coalesce(@out + ', ' + color, color)
	from	m
	where	toy = @toy
	return @out
end
go
-------------------------------------------------

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

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

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


Be One with the Optimizer
TG
Go to Top of Page

mrtsherman
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)
AS
BEGIN
    DECLARE @out varchar(50)
    SELECT @out = COALESCE(@out + ', ' + color, color)
    FROM   m
    WHERE   toy = @toy
    RETURN @out
END


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

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

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

    CREATE FUNCTION dbo.GetToyColors(@toy int)
    RETURNS varchar(50)
    AS
    BEGIN
        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
    End
Go to Top of Page

mrtsherman
Starting Member

3 Posts
Posted - 01/08/2008 :  22:03:48  Show Profile  Reply with Quote
Also this person posted an alternate solution here.

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2651923&SiteID=17&mode=1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 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)



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

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 01/09/2008 :  01:40:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
More explanations
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000