sql server - Split the values and convert rows into columns in SQL -

i have following tables:

1. tblengineeringlookupcolumnmaster

2. tblengineeringlookupdetail

   elccolumnid | elclookupcode | elccolumnname | elcisrequired

   1             |   64              |   firstname |      1 2             |   64              |   lastname  |      1 

eldrecordid | eldlookupcode |eldlookupsequence |eldlookupvalue | eldlookupvaluedescription

245     |     64        |    0         |   red        |    aravinth,arumugam  246     |     64        |    1         |   blue       |    santhosh,chandran  247     |     64        |    2         |   green          |  karthik,balasubramanian 

i need output as:

elclookupcode | eldrecordid | firstname | lastname  ------------------------------------- 64            | 245         | aravinth  |  arumugam 64            | 246         | santhosh  |  chandran     64            | 247         | karthik   |  balasubramanian 

here values in eldlookupvaluedescription firstname,lastname of elccolumnname in tblengineeringlookupcolumnmaster table. have split according elccolumnname rows. if there 3 rows in table have split values of eldlookupvaluedescription accordingly. need handle empty values also.

i have tried splitting values like.

 declare @sqlstr nvarchar(max);  --select initial values select @sqlstr = 'a,b,c,d,e,f'; --replace comma string becomes a','b','c','d','e','f select @sqlstr = replace(@sqlstr, ',', ''',''') --add select beginning , add leading , trailing ' around select values select @sqlstr = 'select ''' + @sqlstr + '''' --execute dynamic sql of select 'a','b','c','d','e','f' exec sp_executesql @sqlstr 

how can achieve this?

note: great if table variables used instead of temptables.

try 1 -

query:

declare @temp table (         elclookupcode int       , eldrecordid int       , txt varchar(100) )  insert @temp (elclookupcode, eldrecordid, txt) values      (64, 245, 'aravinth,arumugam'),     (64, 246, 'santhosh,chandran'),         (64, 247, 'karthik,balasubramanian')  select        t.elclookupcode     , t.eldrecordid     , firstname = txml.value('/t[1]', 'varchar(20)')      , lastname = txml.value('/t[2]', 'varchar(20)')  @temp t join (     select            t2.eldrecordid         , txml = cast('<t>' + replace(txt, ',', '</t><t>') + '</t>' xml)     @temp t2 ) t2 on t.eldrecordid = t2.eldrecordid 

output:

elclookupcode eldrecordid firstname  lastname ------------- ----------- ---------- ---------- 64            245         aravinth   arumugam 64            246         santhosh   chandran 64            247         karthik    balasubram 

---

update

query:

declare @tcol table (       id int identity(1,1)     , elclookupcode int     , elccolumnname varchar(20) )  insert @tcol (elclookupcode, elccolumnname) values      (64, 'firstname'),     (64, 'lastname')  declare @temp table (         elclookupcode int       , eldrecordid int       , txt varchar(100) )  insert @temp (elclookupcode, eldrecordid, txt) values      (64, 245, 'aravinth,arumugam'),     (64, 246, 'santhosh,chandran'),         (64, 247, 'karthik,balasubramanian')  declare @sql nvarchar(max)  ;with cte  (     select            token = ', [' + d2.elccolumnname + '] = ''' + d.token + ''''         , d.eldrecordid     (         select                token = t.c.value('.', 'varchar(50)')             , a.eldrecordid             , a.elclookupcode             , rn = row_number() on (partition a.eldrecordid order a.eldrecordid)         (             select                    eldrecordid                 , elclookupcode                 , txml = cast('<t>' + replace(txt, ',', '</t><t>') + '</t>' xml)             @temp         )         cross apply txml.nodes('/t') t(c)     ) d     join (         select                elclookupcode             , elccolumnname             , rn = row_number() on (partition elclookupcode order elclookupcode)          @tcol      ) d2 on d.elclookupcode = d2.elclookupcode , d2.rn = d.rn ) select @sql = stuff((         select char(13) + 'union select [eldrecordid] = ' + cast(eldrecordid varchar(10)) + ', ' +             '[elclookupcode] = ' + cast(elclookupcode varchar(10)) + ', '+ stuff((         select t2.token         cte t2         t2.eldrecordid = t.eldrecordid         xml path(''), type).value('.', 'varchar(max)'), 1, 2, '')         @temp t     xml path(''), type).value('.', 'varchar(max)'), 1, 11, '')  print @sql exec sys.sp_executesql @sql 

output:

select [eldrecordid] = 245, [elclookupcode] = 64, [firstname] = 'aravinth', [lastname] = 'arumugam' union select [eldrecordid] = 246, [elclookupcode] = 64, [firstname] = 'santhosh', [lastname] = 'chandran' union select [eldrecordid] = 247, [elclookupcode] = 64, [firstname] = 'karthik', [lastname] = 'balasubramanian' 

results:

eldrecordid elclookupcode firstname lastname ----------- ------------- --------- --------------- 245         64            aravinth  arumugam 246         64            santhosh  chandran 247         64            karthik   balasubramanian