need MasterId from comma seperated column values

Question

need MasterId from comma seperated column values

sudhanva

Ten Centuries

Points: 1343
More actions
June 21, 2011 at 12:55 am

#239631

select * into #test1 from (
select 1 ID, 'Dog, Cat' Animal, 'Pet' TypeOfAnimal
union
select 2, 'Tiger, Lion', 'Wild'
) T1
select * into #Master from (
select 1 MasterID, 'Dog' TypeOfAnimal
union
select 2 ID, 'Cat'
union
select 3 ID, 'Tiger'
union
select 4 ID, 'Lion'
) T2
select * from #test1
select * from #Master
I need the out put in below format, with out doing a loop, i need it with a inner join. This is just a sample structure, but my realtime scenario has thousands of records from which i need to fill transaction records, because of which I want to avoid loop.
Please help.
MasterIDAnimalTypeOfAnimal
-------------------------------
1DogPet
2CatPet
3TigerWild
4LionWild

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

select M.*,t.TypeOfAnimal from #Master as m inner join #test1 as t on ', '+t.Animal +', ' like '%, '+m.TypeOfAnimal+', %'

Madhivanan

Failing to plan is Planning to fail

sudhanva Ten Centuries Points: 1343 More actions · Answer 2

Thanks a lot for the awesome logic... 🙂

Some problem with the logic, when the data is like this:

drop table #test1

drop table #Master

select * into #test1 from (

select 1 ID, 'Dog' Animal, 'Pet' TypeOfAnimal

union

select 2, 'Tiger, Lion,elp', 'Wild'

) T1

select * into #Master from (

select 1 MasterID, 'Dog' TypeOfAnimal

union

select 2 ID, 'Cat'

union

select 3 ID, 'Tiger'

union

select 4 ID, 'Lion'

) T2

--select * from #test1

--select * from #Master

select M.*,t.TypeOfAnimal

from #Master as m

inner join #test1 as t on ', '+t.Animal +', ' like '%, '+m.TypeOfAnimal+', %'

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Please have a look at this link:

http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

You can use the function dbo.DelimitedSplit8K to split your comma-separated lists into rows and then join the resulting table to your #master table on TypeOfAnimal to retrieve what would be the #test1.ID for each row in #master.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato