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.
Author |
Topic |
mrtsherman
Starting Member
3 Posts |
Posted - 2008-01-08 : 13:41:40
|
I have two tables joined by a map. For exampleTable1[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, BlueKite - 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 examplecreate 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 concatenationgocreate function dbo.fnConcatColors(@toy varchar(10))returns varchar(50)asbegin declare @out varchar(50) select @out = coalesce(@out + ', ' + color, color) from m where toy = @toy return @outendgo-------------------------------------------------select toy ,dbo.fnConcatColors(t1.toy) [availableColors]from t1---------------------------------------------------clean updrop function dbo.fnConcatColorsdrop table mdrop table t1drop table t2output:toy availableColors---------- --------------------------------------------------Ball Green, BlueFrisbee NULLKite Red Be One with the OptimizerTG |
|
|
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 above1. Go to Server Explorer and connect to your DB2. RMB Functions folder and choose New Function (type doesn't matter)3. Delete everything in the function and cut/paste inCREATE function dbo.fnConcatColors(@toy varchar(10))RETURNS varchar(50)ASBEGIN DECLARE @out varchar(50) SELECT @out = COALESCE(@out + ', ' + color, color) FROM m WHERE toy = @toy RETURN @outEND 4. RMB Stored Procedure folder and choose New. Delete all and cut/paste in:SELECT toy, dbo.fnConcatColors(t1.toy) AS ColorsFROM 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.StoredProcedure1AS 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 |
|
|
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] |
|
|
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 tCROSS APPLY (SELECT m.color + ',' as [text()] FROM #m m WHERE m.toy=t.toy FOR XML PATH(''))Colorlist(c) OutPut:toy AvailableColors---------- ----------------------------------------------------------Ball Green,BlueFrisbee NULLKite Red |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|