Share via

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.
13,361 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,878 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,581 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
  2. EchoLiu-MSFT 14,581 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
  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