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

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Displaying results of MAP

Author  Topic 

mrtsherman
Starting Member

3 Posts

Posted - 2008-01-08 : 13:41:40
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-08 : 15:52:08
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 - 2008-01-08 : 21:54:35
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 - 2008-01-08 : 22:03:48
Also this person posted an alternate solution here.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 23:39:49
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

22864 Posts

Posted - 2008-01-09 : 01:40:40
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
   

- Advertisement -