sql query

Imtiyaz Ansari

12y1.4k

i have two table

1:-table1

id	hobies
1	aa
2	bb
3	cc

2:- table2

id	employee_id	name	lastname
1	1	f1	l1
1,2	2	f2	l2
2,3	3	f3	l3

required result

employee_id	name	hobies
1	f2l1	aa
2	f2l2	aa,bb
3	f3l3	bb,cc

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
Accepted answer
Hi,
Try...
Create table #table1 (id varchar(10), hobies varchar(20))
Create table #table2 (id varchar(10), employee_id varchar(20), name varchar(20), lastname varchar(20))
insert into #table1 values ('1','aa'),
('2','bb'),
('3','cc')
insert into #table2 values
('1','1','f1','l1'),
('1,2','2','f2','l2'),
('2,3','3','f3','l3')

select * from #table1
select * from #table2
select employee_id, name,lastname,id,
(select Stuff((select ',' + hobies from #table1 t
join fn_Split(id, ',') t2 on t.id = t2.Value
for xml path('')),1,1,'')) as hobies
from #table2
here fn_split function is build in function and available with SQL server 2008 and higher.
hope this will help you.

Imtiyaz Ansari12y ago

Thank You Jignesh Trivedi....

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

After created the function you just call the function like this,

Create table #table1 (id varchar(10), hobies varchar(20))

Create table #table2 (id varchar(10), employee_id varchar(20), name varchar(20), lastname varchar(20))

insert into #table1 values ('1','aa'),

('2','bb'),

('3','cc')

insert into #table2 values

('1','1','f1','l1'),

('1,2','2','f2','l2'),

('2,3','3','f3','l3')

select * from #table1

select * from #table2

select employee_id, name,lastname,id,

(select Stuff((select ',' + hobies from #table1 t

join fnSplit(id, ',') t2 on t.id = t2.item

for xml path('')),1,1,'')) as hobies

from #table2

Vasanth Krishnan12y ago
Hi

I am not having dummy table to reproduce the scenerio. But i will let u know the logic.

//this will list all the hobies that are in the id.
Select hobies from table1 where id in (select id from table2)

//Convert this column to Commo seperated by refering this link

http://stackoverflow.com/questions/16193152/sql-server-convert-select-a-column-and-convert-it-to-a-string

Try this one.
Ravi Kumar12y ago
hi
try the fallowing

select employee_id,name+'|'+lastname as name, hobies
from table2 t
inner join table1 tt on t.employee_id=tt.id
Dipankar Biswas12y ago
not clr.....