User49201036 posted
Hi
Find below code that will give you expected output..
create table Table1 (
[Machine Name] varchar(100),
Capacity int
)
insert into Table1
select 'M1',2000
insert into Table1
select 'M2',4000
insert into Table1
select 'M3',5000
insert into Table1
select 'M4',10000
create table Table2 (
[Issued Date] Datetime,
[Machine Name] varchar(100),
[Used Qty] int
)
insert into Table2
select '1-Jun-19','M1',1000
insert into Table2
select '1-Jun-19','M2',4000
insert into Table2
select '1-Jun-19','M3',2000
insert into Table2
select '2-Jun-19','M2',4000
insert into Table2
select '2-Jun-19','M4',8000
DECLARE @totalCols AS NVARCHAR(MAX),@colsName AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @colsName = STUFF((SELECT distinct ',' + QUOTENAME(c.[Machine Name] + '-' + Convert(varchar(100),c.Capacity))
FROM Table1 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @totalCols ='(' + STUFF((SELECT distinct '+ isnull(' + QUOTENAME(c.[Machine Name] + '-' + Convert(varchar(100),c.Capacity)) + ',0) '
FROM Table1 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') + ') as Total'
select @totalCols
select tmp.[Issued Date],t1.[Machine Name],t1.Capacity
into #tmpMachineDetailsIssueDateWise
from table1 t1
cross join (select distinct [Issued Date] from table2 t2) tmp
where not exists (select 1 from table2 t2 where t2.[Issued Date] = tmp.[Issued Date] and t1.[Machine Name] = t2.[Machine Name])
--on t1.[Machine Name] =t2.[Machine Name]
--select @colsName
set @query = 'SELECT [Issued Date],Format, ' + @colsName + ', ' + @totalCols + ' FROM
(
SELECT [Issued Date]
,(t1.[Machine Name] + ''-'' + convert(varchar(100),t1.Capacity)) as [Machine Name]
,''Used'' as Format
,IsNull(T2.[Used Qty],0) as Qty
from table1 t1
left outer join table2 t2
on t1.[Machine Name] =t2.[Machine Name]
union
select [Issued Date]
,(t1.[Machine Name] + ''-'' + convert(varchar(100),t1.Capacity)) as [Machine Name]
,''UnUsed'' as Format
,(isnull(T1.[Capacity],0) -isnull(T2.[Used Qty],0)) as Qty
from table1 t1
left outer join table2 t2
on t1.[Machine Name] =t2.[Machine Name]
union
SELECT [Issued Date]
,([Machine Name] + ''-'' + convert(varchar(100),Capacity)) as [Machine Name]
,''UnUsed'' as Format
,Capacity as Qty
From #tmpMachineDetailsIssueDateWise
) x
pivot
(
max([Qty])
for [Machine Name] in (' + @colsName + ')
) p order by 1,2 desc'
--select @query
exec(@query)
please marked forum as answered if it work for you
