sql query

Question

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

Jignesh Trivedi · 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 Ansari · Answer

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 Krishnan · Answer

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 Kumar · Answer

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 Biswas · Answer

not clr.....

sql query

Answers (5)