Split the values and convert rows into columns in SQL

Question

I have the 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 the output as:

elcLookupCode | eldRecordId | FirstName | LastName 
-------------------------------------
64            | 245         | Aravinth  |  Arumugam
64            | 246         | Santhosh  |  Chandran    
64            | 247         | Karthik   |  Balasubramanian

Here the values in the eldlookupvaluedescription is FirstName,LastName of the elcColumnName in tblengineeringlookupcolumnmaster table. So I have to split according to the elccolumnname rows. If there are 3 rows in that table then I have to split the values of eldlookupvaluedescription accordingly. Need to handle the empty values also.

I have tried to splitting the values like.

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

How can I achieve this?

Note: Will be great if table variables are used instead of TempTables.

Answer 1

Try this one -

Query:

DECLARE @temp TABLE
(
        elcLookupCode INT
      , eldRecordId INT
      , txt VARCHAR(100)
)

INSERT INTO @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)') 
FROM @temp t
JOIN (
    SELECT 
          t2.eldRecordId
        , txml = CAST('<t>' + REPLACE(txt, ',', '</t><t>') + '</t>' AS XML)
    FROM @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 INTO @tcol (elclookupcode, elccolumnname)
VALUES 
    (64, 'FirstName'),
    (64, 'LastName')

DECLARE @temp TABLE
(
        elcLookupCode INT
      , eldRecordId INT
      , txt VARCHAR(100)
)

INSERT INTO @temp (elcLookupCode, eldRecordId, txt)
VALUES 
    (64, 245, 'Aravinth,Arumugam'),
    (64, 246, 'Santhosh,Chandran'),    
    (64, 247, 'Karthik,Balasubramanian')

DECLARE @SQL NVARCHAR(MAX)

;WITH cte AS 
(
    SELECT 
          token = ', [' + d2.elccolumnname + '] = ''' + d.token + ''''
        , d.eldRecordId
    FROM (
        SELECT 
              token = t.c.value('.', 'VARCHAR(50)')
            , a.eldRecordId
            , a.elcLookupCode
            , rn = ROW_NUMBER() OVER (PARTITION BY a.eldRecordId ORDER BY a.eldRecordId)
        FROM (
            SELECT 
                  eldRecordId
                , elcLookupCode
                , txml = CAST('<t>' + REPLACE(txt, ',', '</t><t>') + '</t>' AS XML)
            FROM @temp
        ) a
        CROSS APPLY txml.nodes('/t') t(c)
    ) d
    JOIN (
        SELECT 
              elclookupcode
            , elccolumnname
            , rn = ROW_NUMBER() OVER (PARTITION BY elclookupcode ORDER BY elclookupcode) 
        FROM @tcol 
    ) d2 ON d.elcLookupCode = d2.elclookupcode AND d2.rn = d.rn
)
SELECT @SQL = STUFF((
        SELECT CHAR(13) + 'UNION ALL SELECT [eldRecordId] = ' + CAST(eldRecordId AS VARCHAR(10)) + ', ' + 
           '[elcLookupCode] = ' + CAST(elcLookupCode AS VARCHAR(10)) + ', '+ STUFF((
        SELECT t2.token
        FROM cte t2
        WHERE t2.eldRecordId = t.eldRecordId
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')
        FROM @temp t
    FOR 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 ALL SELECT [eldRecordId] = 246, [elcLookupCode] = 64, [FirstName] = 'Santhosh', [LastName] = 'Chandran'
UNION ALL 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