Help in looking values from Lookup table

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 2008 Forums

Transact-SQL (2008)

Help in looking values from Lookup table

Author

Topic

vipulpatel
Starting Member

2 Posts

Posted - 2014-10-02 : 17:26:31

I have a table 1 like
ID Region MarketID
10 Northwest 1
20 East 2,3
30 West 3
40 North 4
50 West 1,2

Table 2
MarketId Name
1 Atlanta
2 Seattle
3 Chicago
4 Kansas

Result: I am looking for is as below...

ID Region MarketID
10 Northwest Atlanta
20 East Seattle,Chicago
30 West Chicago
40 North Kansas
50 West Atlanta,Seattle

Basicall I want to Replace MarketID in Table1 with it's respective names from Table2, How can I do it? Please help.. Thankyou..

AASC
Starting Member

24 Posts

Posted - 2014-10-03 : 01:45:05

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0
BREAK
END
RETURN
END

GO
-----------------------------------------------------
-----------------------------------------------------

select ID,Region,
MarketID= stuff((SELECT ', ' + name
FROM table1
CROSS APPLY dbo.[fnSplit](table1.MarketId,',') AS b
INNER JOIN tabl2 ON tabl2.MarketID=b.item
where outertable1.id=table1.id
FOR
XML PATH('') ), 1, 1,'')
from table1 outertable1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-03 : 09:32:04

[code]
select t1.id, t1.region, t2.name
from table1 t1
join table2 t2
on t1.marketid = t2.marketid
[/code]

vipulpatel
Starting Member

2 Posts

Posted - 2014-10-03 : 14:42:14

Thank you so much AASC, it worked and i really appreciate it. Thanks again. Thanks Gbritton as well for reply.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources