Query is not giving me expected output.

Analyst_SQL 3,551 Reputation points
2021-02-04T07:33:24.05+00:00

Below is query with data,which is not giving me expected output.

 DECLARE @StartDate  date = '03-06-2020';  
  DECLARE @enddate date = '06-06-2020';  
      
 Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50))  
  Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,delid int)  
  Create table #DispatchBM (DID int,Name varchar(50),Date date,Del int)  
  Create table #Dispatch_BD (ID int ,BID int,DID int,Codeitem int,QTY int,BWeight int,Delidd int)  
          
  INSERT INTO #ItemMasterFile VALUES  
    (1,'A','Bigbale')  
  , (2,'B','Bigbale')  
  , (3,'C','Bigbale')  
  , (4,'D','Bigbale')  
  , (5,'e','Bigbale')  
  , (6,'f','Bigbale')  
  , (7,'g','Bigbale')  
  , (8,'h','Bigbale')  
  , (9,'K','Bigbale')  
  , (10,'L','Bigbale')  
  , (11,'M','Bigbale');  
          
          
  INSERT INTO #Bigbalprd VALUES  
  (111,1,1,500,'03-06-2020',null)  
  ,(112,2,1,200,'03-06-2020',null)  
  ,(113,1,1,300,'03-06-2020',null)  
  ,(114,6,1,100,'04-06-2020',null)  
  ,(115,1,1,200,'04-06-2020',null)  
  ,(116,1,1,300,'04-06-2020',null)  
  ,(117,7,1,100,'05-06-2020',null)  
  ,(118,5,1,200,'05-06-2020',null)  
  ,(119,8,1,300,'06-06-2020',null)  
          
  Insert into #DispatchBM Values  
  (1001,'Akhter','03-06-2020',null)  
  ,(1002,'Irfan','06-06-2020',null)  
  Insert into #Dispatch_BD Values  
  (11,111,1001,1,1,500,null)  
  ,(12,112,1001,2,1,200,null)  
  ,(13,113,1001,1,1,300,null)  
  ,(14,117,1002,7,1,100,null)  
  ,(15,118,1002,5,1,200,null)  
      
  
;with cte as (  
select a.CodeItem ,upper(a.Descriptionitem) item_Name,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight  
from #ItemMasterFile a  
left join #Bigbalprd b   
on a.CodeItem=b.CodeItem  
where convert(date,b.EntryDate,105) between @startdate and @enddate and b.delID is null  
group by a.CodeItem,a.Descriptionitem,b.EntryDate  
)  
,cte1 as (  
select a.CodeItem,upper(a.Descriptionitem) item_Name,  Date,sum(qty) D_QTY,sum(Bweight) D_Weight  
from #ItemMasterFile a  
left join #Dispatch_BD c  
on c.codeitem=a.codeitem  
left join #DispatchBM d  
on d.DID=c.DID   
where convert(date,date,105) between @startdate and @enddate and c.Delidd is null and d.Del is null  
group by a.codeitem,a.Descriptionitem,d.date  
),cte2 as (  
select f.CodeItem,f.item_Name,(f.Bigbale_QTY-f.Dispatch_QTY) as Balance_Qty,(f.Bigbale_weight-f.Dispatch_Weight) as Balance_Weight from (  
select e.CodeItem,e.item_Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],  
isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (  
select upper(a.Descriptionitem)  item_Name,(a.CodeItem)  CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]  
from #ItemMasterFile a  
--inner join Catagory ca on ca.CID=a.CID  
left join #Bigbalprd b on a.CodeItem=b.CodeItem  
 where a.Packsize ='bigbale' and b.delID is null    
  group by a.Descriptionitem,a.CodeItem) e  
left join #Dispatch_BD c on e.CodeItem=c.CodeItem   
 where c.Delidd is null    
group by e.item_Name,e.CodeItem  
  
)f)  
  
select c.codeitem,upper(c.Descriptionitem) item_Name,B_Date,isnull(B_QTY,0) B_QTY,isnull(B_Weight,0) B_Weight,isnull(D_QTY,0) D_QTY,isnull(D_Weight,0) D_Weight  
,isnull(Balance_Qty,0) Balance_Qty,isnull(Balance_Weight,0) Balance_Weight  
into #t  
from #ItemMasterFile c   
left join cte a on a.codeitem=c.codeitem  
left join cte1 b on a.codeitem=b.codeitem   
left  join cte2 d on a.CodeItem=d.CodeItem  
and a.B_Date=b.date  
where c.Packsize='Bigbale'  
DECLARE @cols NVARCHAR (MAX)  
  
SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'  
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
set @cols=SUBSTRING(@cols,2,len(@cols)-1)  
  
DECLARE @cols1 NVARCHAR (MAX)  
  
SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'  
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'  
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'  
+',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)  
  
DECLARE @cols2 NVARCHAR (MAX)  
  
SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'  
+',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
DECLARE @cols3 NVARCHAR (MAX)  
  
SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23)  +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'  
+',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '  
                from #t where ISNULL(B_date,'')<>'' for xml path(''))  
  
set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)  
  
DECLARE @query NVARCHAR(MAX)  
SET @query = '    
  
select '''' codeitem,'''' item_Name,'''' Balance_QTY,'''' Balance_Weight,'+@cols3+'  
UNION ALL  
SELECT cast(codeitem as varchar(10)) codeitem,item_Name,Balance_QTY,Balance_Weight,' + @cols1 + '  
            FROM (  
    SELECT codeitem,item_Name,Balance_QTY,Balance_Weight,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE  
    FROM (  
        select * from #t  
    )s  
    UNPIVOT  
    (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p  
) src  
             PIVOT   
             (  
			MAX(VALUE) FOR Name IN (' + @cols + ')  
) pvt  
            '     
EXEC SP_EXECUTESQL @query  

Expected output

63789-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,917 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,829 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

3 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-02-08T07:45:51.53+00:00

    Hi @Analyst_SQL ,

    65185-image.png
    Is the following output the result you expect:

         with cte  
         as(select '' codeitem,'' item_Name,'' Balance_QTY,'' Balance_Weight,'B_Qty' [2020-03-06],'B_Weight' [2020-03-061],'D_QTY' [2020-03-062] ,'D_Weight' [2020-03-063] ,'B_Qty' [2020-04-061],'B_Weight' [2020-04-062],'D_QTY' [2020-04-063] ,'D_Weight' [2020-04-064] ,'B_Qty' [2020-05-06],'B_Weight' [2020-05-061],'D_QTY' [2020-05-062] ,'D_Weight' [2020-05-063] ,'B_Qty' [2020-06-061],'B_Weight' [2020-06-062],'D_QTY' [2020-06-063] ,'D_Weight' [2020-06-064]  
         UNION ALL  
         SELECT cast(codeitem as varchar(10)) codeitem,item_Name,Balance_QTY,Balance_Weight,cast(ISNULL([2020-03-06 B_QTY],0) as varchar(10))  [2020-03-06 B_QTY],cast(ISNULL([2020-03-06 B_Weight],0) as varchar(10))  [2020-03-06 B_Weight],cast(ISNULL([2020-03-06 D_QTY],0) as varchar(10))  [2020-03-06 D_QTY],cast(ISNULL([2020-03-06 D_Weight],0) as varchar(10))  [2020-03-06 D_Weight],cast(ISNULL([2020-04-06 B_QTY],0) as varchar(10))  [2020-04-06 B_QTY],cast(ISNULL([2020-04-06 B_Weight],0) as varchar(10))  [2020-04-06 B_Weight],cast(ISNULL([2020-04-06 D_QTY],0) as varchar(10))  [2020-04-06 D_QTY],cast(ISNULL([2020-04-06 D_Weight],0) as varchar(10))  [2020-04-06 D_Weight],cast(ISNULL([2020-05-06 B_QTY],0) as varchar(10))  [2020-05-06 B_QTY],cast(ISNULL([2020-05-06 B_Weight],0) as varchar(10))  [2020-05-06 B_Weight],cast(ISNULL([2020-05-06 D_QTY],0) as varchar(10))  [2020-05-06 D_QTY],cast(ISNULL([2020-05-06 D_Weight],0) as varchar(10))  [2020-05-06 D_Weight],cast(ISNULL([2020-06-06 B_QTY],0) as varchar(10))  [2020-06-06 B_QTY],cast(ISNULL([2020-06-06 B_Weight],0) as varchar(10))  [2020-06-06 B_Weight],cast(ISNULL([2020-06-06 D_QTY],0) as varchar(10))  [2020-06-06 D_QTY],cast(ISNULL([2020-06-06 D_Weight],0) as varchar(10))  [2020-06-06 D_Weight]  
                     FROM (  
             SELECT codeitem,item_Name,Balance_QTY,Balance_Weight,  CAST(B_Date AS VARCHAR) + ' '+ITEM AS Name, VALUE  
             FROM (  
                 select * from #t  
             )s  
             UNPIVOT  
             (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p  
         ) src  
                      PIVOT   
                      (  
                     MAX(VALUE) FOR Name IN ([2020-03-06 B_QTY],[2020-03-06 B_Weight],[2020-03-06 D_QTY],[2020-03-06 D_Weight],[2020-04-06 B_QTY],[2020-04-06 B_Weight],[2020-04-06 D_QTY],[2020-04-06 D_Weight],[2020-05-06 B_QTY],[2020-05-06 B_Weight],[2020-05-06 D_QTY],[2020-05-06 D_Weight],[2020-06-06 B_QTY],[2020-06-06 B_Weight],[2020-06-06 D_QTY],[2020-06-06 D_Weight])  
         ) pvt)  
        ,cte2 as  
        (  
             select f.CodeItem,f.item_Name,(f.Bigbale_QTY-f.Dispatch_QTY) as Balance_Qty,(f.Bigbale_weight-f.Dispatch_Weight) as Balance_Weight from (  
             select e.CodeItem,e.item_Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],  
             isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (  
             select upper(a.Descriptionitem)  item_Name,(a.CodeItem)  CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]  
             from #ItemMasterFile a  
             --inner join Catagory ca on ca.CID=a.CID  
             left join #Bigbalprd b on a.CodeItem=b.CodeItem  
              where a.Packsize ='bigbale' and b.delID is null    
               group by a.Descriptionitem,a.CodeItem) e  
             left join #Dispatch_BD c on e.CodeItem=c.CodeItem   
              where c.Delidd is null    
             group by e.item_Name,e.CodeItem  
                  
             )f)  
        select c1.codeitem,c1.item_Name,c2.Balance_QTY,c2.Balance_Weight,c1.[2020-03-06],c1.[2020-03-061],c1.[2020-03-062] ,c1.[2020-03-063] ,c1.[2020-04-061],c1.[2020-04-062],c1.[2020-04-063] ,c1.[2020-04-064] ,c1.[2020-05-06],c1.[2020-05-061],c1.[2020-05-062] ,c1.[2020-05-063] ,c1.[2020-06-061],c1.[2020-06-062],c1.[2020-06-063] ,c1.[2020-06-064]  
        from cte c1  
        left join cte2 c2  
        on c1.CodeItem=c2.CodeItem  
    

    65272-image.png

    1 person found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-02-05T08:20:36.307+00:00

    Hi @Analyst_SQL ,

    I use print to output all the contents of your variables. Please check if you can get the results you expect by modifying these statements:

     DECLARE @cols NVARCHAR (MAX)  
          
     SET @cols = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'  
     +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'  
                     from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
     set @cols=SUBSTRING(@cols,2,len(@cols)-1)  
          
     DECLARE @cols1 NVARCHAR (MAX)   
          
     SET @cols1 = (SELECT DISTINCT ',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY]'  
     +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight]'  
     +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY]'  
     +',cast(ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) as varchar(10)) ' +' [' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight]'  
                     from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
     set @cols1=SUBSTRING(@cols1,2,len(@cols1)-1)  
      
     DECLARE @cols2 NVARCHAR (MAX)  
          
     SET @cols2 = (SELECT DISTINCT ',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight] NVARCHAR(1000)'  
     +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY] NVARCHAR(1000)' +',[' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight] NVARCHAR(1000)'  
                     from #t where ISNULL(B_date,'')<>'' for xml path(''))  
       
     DECLARE @cols3 NVARCHAR (MAX)  
          
     SET @cols3 = (SELECT DISTINCT ',''B_Qty'' [' + CONVERT(NVARCHAR, B_date, 23)  +'],''B_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + ']'  
     +',''D_QTY'' [' + CONVERT(NVARCHAR, B_date, 23) + '] ' +',''D_Weight'' [' + CONVERT(NVARCHAR, B_date, 23) + '] '  
                     from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
     set @cols3=SUBSTRING(@cols3,2,len(@cols3)-1)  
          
     DECLARE @query NVARCHAR(MAX)  
     SET @query = '    
          
     select '''' codeitem,'''' item_Name,'+@cols3+'  
     UNION ALL  
     SELECT cast(codeitem as varchar(10)) codeitem,item_Name,' + @cols1 + '  
                 FROM (  
         SELECT codeitem,item_Name,  CAST(B_Date AS VARCHAR) + '' ''+ITEM AS Name, VALUE  
         FROM (  
             select * from #t  
         )s  
         UNPIVOT  
         (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p  
     ) src  
                  PIVOT   
                  (  
                 MAX(VALUE) FOR Name IN (' + @cols + ')  
     ) pvt  
                 '   
     print @cols1     
     print @cols   
     print @cols2     
     print @cols3     
     print @query   
    

    Output:

        cast(ISNULL([2020-03-06 B_QTY],0) as varchar(10))  [2020-03-06 B_QTY],cast(ISNULL([2020-03-06 B_Weight],0) as varchar(10))  [2020-03-06 B_Weight],cast(ISNULL([2020-03-06 D_QTY],0) as varchar(10))  [2020-03-06 D_QTY],cast(ISNULL([2020-03-06 D_Weight],0) as varchar(10))  [2020-03-06 D_Weight],cast(ISNULL([2020-04-06 B_QTY],0) as varchar(10))  [2020-04-06 B_QTY],cast(ISNULL([2020-04-06 B_Weight],0) as varchar(10))  [2020-04-06 B_Weight],cast(ISNULL([2020-04-06 D_QTY],0) as varchar(10))  [2020-04-06 D_QTY],cast(ISNULL([2020-04-06 D_Weight],0) as varchar(10))  [2020-04-06 D_Weight],cast(ISNULL([2020-05-06 B_QTY],0) as varchar(10))  [2020-05-06 B_QTY],cast(ISNULL([2020-05-06 B_Weight],0) as varchar(10))  [2020-05-06 B_Weight],cast(ISNULL([2020-05-06 D_QTY],0) as varchar(10))  [2020-05-06 D_QTY],cast(ISNULL([2020-05-06 D_Weight],0) as varchar(10))  [2020-05-06 D_Weight]  
        [2020-03-06 B_QTY],[2020-03-06 B_Weight],[2020-03-06 D_QTY],[2020-03-06 D_Weight],[2020-04-06 B_QTY],[2020-04-06 B_Weight],[2020-04-06 D_QTY],[2020-04-06 D_Weight],[2020-05-06 B_QTY],[2020-05-06 B_Weight],[2020-05-06 D_QTY],[2020-05-06 D_Weight]  
        ,[2020-03-06 B_QTY] NVARCHAR(1000),[2020-03-06 B_Weight] NVARCHAR(1000),[2020-03-06 D_QTY] NVARCHAR(1000),[2020-03-06 D_Weight] NVARCHAR(1000),[2020-04-06 B_QTY] NVARCHAR(1000),[2020-04-06 B_Weight] NVARCHAR(1000),[2020-04-06 D_QTY] NVARCHAR(1000),[2020-04-06 D_Weight] NVARCHAR(1000),[2020-05-06 B_QTY] NVARCHAR(1000),[2020-05-06 B_Weight] NVARCHAR(1000),[2020-05-06 D_QTY] NVARCHAR(1000),[2020-05-06 D_Weight] NVARCHAR(1000)  
        'B_Qty' [2020-03-06],'B_Weight' [2020-03-06],'D_QTY' [2020-03-06] ,'D_Weight' [2020-03-06] ,'B_Qty' [2020-04-06],'B_Weight' [2020-04-06],'D_QTY' [2020-04-06] ,'D_Weight' [2020-04-06] ,'B_Qty' [2020-05-06],'B_Weight' [2020-05-06],'D_QTY' [2020-05-06] ,'D_Weight' [2020-05-06]  
            
              
         select '' codeitem,'' item_Name,'B_Qty' [2020-03-06],'B_Weight' [2020-03-06],'D_QTY' [2020-03-06] ,'D_Weight' [2020-03-06] ,'B_Qty' [2020-04-06],'B_Weight' [2020-04-06],'D_QTY' [2020-04-06] ,'D_Weight' [2020-04-06] ,'B_Qty' [2020-05-06],'B_Weight' [2020-05-06],'D_QTY' [2020-05-06] ,'D_Weight' [2020-05-06]  
         UNION ALL  
         SELECT cast(codeitem as varchar(10)) codeitem,item_Name,cast(ISNULL([2020-03-06 B_QTY],0) as varchar(10))  [2020-03-06 B_QTY],cast(ISNULL([2020-03-06 B_Weight],0) as varchar(10))  [2020-03-06 B_Weight],cast(ISNULL([2020-03-06 D_QTY],0) as varchar(10))  [2020-03-06 D_QTY],cast(ISNULL([2020-03-06 D_Weight],0) as varchar(10))  [2020-03-06 D_Weight],cast(ISNULL([2020-04-06 B_QTY],0) as varchar(10))  [2020-04-06 B_QTY],cast(ISNULL([2020-04-06 B_Weight],0) as varchar(10))  [2020-04-06 B_Weight],cast(ISNULL([2020-04-06 D_QTY],0) as varchar(10))  [2020-04-06 D_QTY],cast(ISNULL([2020-04-06 D_Weight],0) as varchar(10))  [2020-04-06 D_Weight],cast(ISNULL([2020-05-06 B_QTY],0) as varchar(10))  [2020-05-06 B_QTY],cast(ISNULL([2020-05-06 B_Weight],0) as varchar(10))  [2020-05-06 B_Weight],cast(ISNULL([2020-05-06 D_QTY],0) as varchar(10))  [2020-05-06 D_QTY],cast(ISNULL([2020-05-06 D_Weight],0) as varchar(10))  [2020-05-06 D_Weight]  
                     FROM (  
             SELECT codeitem,item_Name,  CAST(B_Date AS VARCHAR) + ' '+ITEM AS Name, VALUE  
             FROM (  
                 select * from #t  
             )s  
             UNPIVOT  
             (VALUE FOR Item IN ([B_QTY], [B_Weight], [D_QTY],[D_Weight])) p  
         ) src  
                      PIVOT   
                      (  
                     MAX(VALUE) FOR Name IN ([2020-03-06 B_QTY],[2020-03-06 B_Weight],[2020-03-06 D_QTY],[2020-03-06 D_Weight],[2020-04-06 B_QTY],[2020-04-06 B_Weight],[2020-04-06 D_QTY],[2020-04-06 D_Weight],[2020-05-06 B_QTY],[2020-05-06 B_Weight],[2020-05-06 D_QTY],[2020-05-06 D_Weight])  
         ) pvt  
    

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Analyst_SQL 3,551 Reputation points
    2021-02-08T07:57:42.83+00:00

    @EchoLiu-MSFT

    Below output i need ,Above your out put is coming wrong
    65256-bb.png

    0 comments No comments