CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
MAK
MAK

Reputation: 7260

Split multiple column and store into temp table

I have the sample data:

Table: tblsampledata

create table tblsampledata
(
    column1 varchar(50),
    column2 varchar(50)
);

insert into tblsampledata values('Bob Frapples','Gail Forcewind');
insert into tblsampledata values('Paul Molive','Mario Speedwagon');

And I have column mapping table with table name:

Table: tblmapping

create table tblmapping
(
    tblname varchar(100),
    columnmap varchar(max)
);

insert into tblmapping values('tblsampledata','[column1]|[column2]');

Note: I want to split the column data which are exists in tblmapping of table name in column tblname and store it into temp table.

Expected Result: #TempTable

column1     column2
---------------------
Bob         Gail
Frapples    Forcewind
Paul        Mario
Molive      Speedwagon

Upvotes: 0

Views: 266

Answers (1)

PSK
PSK

Reputation: 17943

You need to use dynamic query to acheive this.

You can try like following.

select @xml = Cast(( '<X>' + Replace(columnmap, '|', '</X><X>') + '</X>' ) AS XML) 
from tblmapping where tblname =@tablename


DECLARE @query AS NVARCHAR(max) = 'select ' +  Stuff((SELECT DISTINCT ', ' + value
            FROM   (
            SELECT n.value('.', 'varchar(100)') AS value 
            FROM   @xml.nodes('X') AS T(n)
        )t
            FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
                  + ' from ' + @tablename; 
exec sp_executesql @query

Online Demo

To split the column 1 and Column 2 you can use query like following.

SELECT CASE 
         WHEN n = 1 THEN LEFT(column1, Charindex(' ', column1) - 1) 
         WHEN n = 2 THEN RIGHT(column1, Charindex(' ', Reverse(column1)) - 1) 
       END AS column1, 
       CASE 
         WHEN n = 1 THEN LEFT(column2, Charindex(' ', column2) - 1) 
         WHEN n = 2 THEN RIGHT(column2, Charindex(' ', Reverse(column2)) - 1) 
       END AS column2 
FROM   tblsampledata t1 
       CROSS JOIN ( VALUES(1),(2) )t(n)

Full Demo using dynamic query

Upvotes: 3

Related Questions