CODEWITHSUNDEEP
sql-server
user2516261
user2516261

Reputation: 73

joining two datatable which have dynamic column using stored procedure in dynamic sql

How can I join (inner) the two datatable dt1 and dt2 that have dynamic coloumn. using stored procedure in dynamic sql.

dt1 contains

emp id empname SickLeave  Casualleave
 1      h        1

dt2 contains

empid empname SickLeave Casualleave
1       h       5         5

I have to show output like

empid empname SickLeave Casualleave
1       h      1/5         0/5

Please guide me

Thanks

Upvotes: 0

Views: 464

Answers (4)

jpw
jpw

Reputation: 44881

So maybe this will do what you want. This query gets all the column names from sys.columns named like '%leave%' where the table is either dt1or dt2 and builds a dynamic query that it then executes. It relies on the sys.columns and sys.tables (which might be bad) and has the source table names dt1and dt2hard coded.

In my opinion this is not a good solution, and the correct solution to the problem might be to alter the data model so that leavebecomes an entity of it's own.

I've commented out the EXECUTEat the end and left the PRINTin so you can see what the query will do before executing it.

DECLARE @Columns VARCHAR(MAX)
SELECT @Columns = COALESCE(@Columns + ',' + name + '', '' + name + '') FROM (
    SELECT DISTINCT 
    'CAST(ISNULL(dt1.' + name + ',0) AS VARCHAR) + ''/'' + CAST(dt2.' + name + ' AS VARCHAR) AS ' + name + ' ' AS name 
FROM sys.columns 
WHERE NAME LIKE '%leave%' 
AND object_id IN (SELECT object_id FROM sys.tables WHERE name IN ('dt1', 'dt2'))) LeaveColumns

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
SELECT 
dt1.empid, 
dt1.empname, ' + @Columns + ' 
FROM dt1 
INNER JOIN dt2 ON dt1.empid=dt2.empid'    

PRINT @SQL -- Uncomment to see the query which will be run
--EXECUTE(@SQL)

For me this gives the following output (with a slightly altered table structure where I included more leavecolumns):

SELECT 
dt1.empid, 
dt1.empname, 
CAST(ISNULL(dt1.casualleave,0) AS VARCHAR) + '/' + CAST(dt2.casualleave AS VARCHAR) AS casualleave ,
CAST(ISNULL(dt1.sickleave,0) AS VARCHAR) + '/' + CAST(dt2.sickleave AS VARCHAR) AS sickleave ,
CAST(ISNULL(dt1.someotherleave,0) AS VARCHAR) + '/' + CAST(dt2.someotherleave AS VARCHAR) AS someotherleave ,
CAST(ISNULL(dt1.yetanotherleave,0) AS VARCHAR) + '/' + CAST(dt2.yetanotherleave AS VARCHAR) AS yetanotherleave  
FROM dt1 
INNER JOIN dt2 ON dt1.empid=dt2.empid

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

select t1.[empid],t1.[empname],
Cast(COALESCE(t1.[SickLeave],0) as char)
     +'/'
     +cast(COALESCE(t2.[SickLeave],0) as char) as SickLeave,
Cast(COALESCE(t1.[Casualleave],0) as char)
+'/'
+cast(COALESCE(t2.[Casualleave],0) as char) as Casualleave
from Table1 t1 inner join Table2 t2 on 
t1.[empid]=t2.[empid];

fiddle

Upvotes: 0

gvee
gvee

Reputation: 17161

You don't need dynamic SQL...

SELECT emp_id
     , empname
     , dt1_sickleave   + '/' + dt2_sickleave   As sickleave
     , dt1_casualleave + '/' + dt2_casualleave As casualleave
FROM   (
        SELECT Coalesce(dt1.emp_id, dt2.emp_id) As emp_id
             , Coalesce(dt1.empname, dt2.empname) As empname
             , Cast(Coalesce(dt1.sickleave  , 0) As varchar(10)) As dt1_sickleave
             , Cast(Coalesce(dt1.casualleave, 0) As varchar(10)) As dt1_casualleave
             , Cast(Coalesce(dt2.sickleave  , 0) As varchar(10)) As dt2_sickleave
             , Cast(Coalesce(dt2.casualleave, 0) As varchar(10)) As dt2_casualleave
        FROM   dt1
         FULL
          JOIN dt2
            ON dt2.emp_id = dt1.emp_id
       ) As x

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

You can do this using STUFF() like this:

WITH CTE(empid, empname, SickLeave, Casualleave)
AS (SELECT * FROM dt1
    UNION ALL
    SELECT * FROM dt2
   )
SELECT distinct empid, empname
, SickLeave = 
    STUFF((SELECT ' / ' + CONVERT(VARCHAR(20),[SickLeave])
           FROM CTE b 
           WHERE b.empid = a.empid
          FOR XML PATH('')), 1, 2, '')
, Casualleave = 
    STUFF((SELECT ' / ' + CONVERT(VARCHAR(20),[Casualleave])
           FROM CTE C 
           WHERE C.empid = a.empid
          FOR XML PATH('')), 1, 2, '')
FROM CTE a
GROUP BY Empid, empname;

Output:

| EMPID | EMPNAME | SICKLEAVE | CASUALLEAVE |
---------------------------------------------
|     1 |       h |     1 / 5 |       0 / 5 |

See this SQLFiddle

Upvotes: 1

Related Questions