add category column ,in pivot query.

Question

add category column ,in pivot query.

Analyst_SQL 3,576

@MelissaMa-msft ,
below is query with data, i want to add category column,but category (CID) will fetch(Join) from #Bigbaleprd table.

drop table if exists #ItemMasterFile  
 drop table if exists #Bigbalprd  
 drop table if exists #DispatchBM  
 drop table if exists #DispatchDB  
  drop table if exists #Catagory  
 drop table if exists #t  
      
 Create table #Catagory (CID int,CName varchar(50))  
 Create table #ItemMasterFile (item_ID int,item_Name varchar(50),CID int)  
 Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date,CID int)  
 Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)  
 Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)  
   
 Insert into #Catagory values  
 (1,'Cat1'),  
 (2,'Cat2'),  
 (3,'Cat3'),  
 (4,'Cat4')  
      
 INSERT INTO #ItemMasterFile VALUES  
   (1,'A',1)  
 , (2,'B',1)  
 , (3,'C',2)  
 , (4,'D',3)  
 , (5,'e',3)  
 , (6,'f',2)  
 , (7,'g',3)  
 , (8,'h',4)  
 , (9,'K',4)  
 , (10,'L',4)  
 , (11,'M',4);  
      
      
 INSERT INTO #Bigbalprd VALUES  
 (111,1,1,500,'03-06-2020',1)  
 ,(112,2,1,200,'03-06-2020',1)  
 ,(113,1,1,300,'03-06-2020',1)  
 ,(114,6,1,100,'04-06-2020',2)  
 ,(115,1,1,200,'04-06-2020',1)  
 ,(116,1,1,300,'04-06-2020',1)  
 ,(117,7,1,100,'05-06-2020',3)  
 ,(118,5,1,200,'05-06-2020',3)  
 ,(119,8,1,300,'06-06-2020',4)  
      
 Insert into #DispatchBM Values  
 (1001,'Akhter','03-06-2020')  
 ,(1002,'Irfan','05-06-2020')  
 Insert into #DispatchDB Values  
 (11,1001,1,1,500)  
 ,(12,1001,2,1,200)  
 ,(13,1001,1,1,300)  
 ,(14,1002,7,1,100)  
 ,(15,1002,5,1,200)  
      
 DECLARE @StartDate  date = '03-06-2020';  
 DECLARE @enddate date = '06-06-2020';  
      
 ;with cte as (  
  
 select a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight  
 from #ItemMasterFile a  
 left join #Bigbalprd b   
 on a.item_ID=b.item_ID  
 where convert(date,B_Date,105) between @startdate and @enddate  
 group by a.item_ID,a.item_Name,B_Date  
 )  
 ,cte1 as (  
 select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight  
 from #ItemMasterFile a  
 left join #DispatchDB c  
 on c.item_ID=a.item_ID  
 left join #DispatchBM d  
 on d.D_ID=c.D_ID   
 where convert(date,D_Date,105) between @startdate and @enddate  
 group by a.item_ID,a.item_Name,d.D_Date  
 )  
      
 select c.item_ID,upper(c.item_Name) 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  
 into #t  
 from #ItemMasterFile c   
 left join cte a on a.item_ID=c.item_ID  
 left join cte1 b on a.item_ID=b.item_ID  
 and a.B_Date=b.D_Date  
      
 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 @SUM1 NVARCHAR (MAX)  
      
 SET @SUM1=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) +'  
                 from #t where ISNULL(B_date,'')<>'' for xml path(''))  
      
 SET @SUM1=LEFT(@SUM1,LEN(@SUM1)-1)  
      
 DECLARE @SUM2 NVARCHAR (MAX)  
      
 SET @SUM2=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) +'  
                 from #t where ISNULL(B_date,'')<>'' for xml path(''))  
      
 SET @SUM2=LEFT(@SUM2,LEN(@SUM2)-1)  
      
 DECLARE @SUM3 NVARCHAR (MAX)  
      
 SET @SUM3=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) +'  
                 from #t where ISNULL(B_date,'')<>'' for xml path(''))  
      
 SET @SUM3=LEFT(@SUM3,LEN(@SUM3)-1)  
      
 DECLARE @SUM4 NVARCHAR (MAX)  
      
 SET @SUM4=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) +'  
                 from #t where ISNULL(B_date,'')<>'' for xml path(''))  
      
 SET @SUM4=LEFT(@SUM4,LEN(@SUM4)-1)  
      
 DECLARE @SUM NVARCHAR (MAX)  
      
 SET @SUM='CAST(SUM('+@SUM1+')-SUM('+@SUM2+') AS VARCHAR),CAST(SUM('+@SUM3+')-SUM('+@SUM4+')AS VARCHAR),'  
      
 DECLARE @query NVARCHAR(MAX)  
 SET @query = '    
      
 select '''' item_id,'''' item_Name,'''' [Balance Bale],'''' [Balance Lbs.],'+@cols3+'  
 UNION ALL  
 SELECT cast(item_id as varchar(10)) item_id,item_Name,'+@SUM + @cols1 + '  
             FROM (  
     SELECT item_ID,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  
 group by item_id,item_Name,' + @cols   
                  
 EXEC SP_EXECUTESQL @query

Answer accepted by question author

4 additional answers

Your answer

Answer 1

Hi @Analyst_SQL ,

If above query is still not working, please try with below:

DECLARE @StartDate  date = '03-06-2020';  
   DECLARE @enddate date = '06-06-2020';  
              
;with cte as (  
              
    select  c.CName category ,a.codeitem,upper(a.Descriptionitem) Descriptionitem,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight  
    from ItemMasterFile a  
    left join Bigbalprd b   
    on a.codeitem=b.codeitem  
    inner join Catagory c   
    on c.cid=isnull(b.CID,a.cid)  
    where convert(date,b.EntryDate,105) between @startdate and @enddate or b.entrydate is null  
    group by a.codeitem,b.entrydate,c.CName,a.Descriptionitem  
    )  
   ,cte1 as (  
   select a.codeitem,upper(a.Descriptionitem) Descriptionitem,  (d.date) D_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,d.date,105) between @startdate and @enddate and a.Packsize='Bigbale'    
   group by a.codeitem,a.Descriptionitem,d.date  
   )  
         
   select isnull(a.category,d.CName) category, c.codeitem,upper(c.Descriptionitem) Descriptionitem,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  
   into #t  
   from ItemMasterFile c   
   left join cte a on a.codeitem=c.codeitem  
   left join cte1 b on a.codeitem=b.codeitem  
   and a.B_Date=b.D_Date  
   left join  Catagory d on d.cid=c.CID  
    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 @SUM1 NVARCHAR (MAX)  
              
   SET @SUM1=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM1=LEFT(@SUM1,LEN(@SUM1)-1)  
              
   DECLARE @SUM2 NVARCHAR (MAX)  
              
   SET @SUM2=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM2=LEFT(@SUM2,LEN(@SUM2)-1)  
              
   DECLARE @SUM3 NVARCHAR (MAX)  
              
   SET @SUM3=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM3=LEFT(@SUM3,LEN(@SUM3)-1)  
              
   DECLARE @SUM4 NVARCHAR (MAX)  
              
   SET @SUM4=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM4=LEFT(@SUM4,LEN(@SUM4)-1)  
              
   DECLARE @SUM NVARCHAR (MAX)  
              
   SET @SUM='CAST(SUM('+@SUM1+')-SUM('+@SUM2+') AS VARCHAR),CAST(SUM('+@SUM3+')-SUM('+@SUM4+')AS VARCHAR),'  
              
   DECLARE @query NVARCHAR(MAX)  
   SET @query = '    
              
   select '''' category,'''' codeitem,'''' Descriptionitem,'''' [Balance Bale],'''' [Balance Lbs.],'+@cols3+'  
   UNION ALL  
   SELECT cast(category as varchar(50)) category,cast(codeitem as varchar(10)) codeitem,Descriptionitem,'+@SUM + @cols1 + '  
               FROM (  
       SELECT category,codeitem,Descriptionitem,  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  
   group by codeitem,Descriptionitem,category,' + @cols   
                          
   EXEC SP_EXECUTESQL @query  
  
  
   drop table #t

Best regards
Melissa

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.

Analyst_SQL 3,576 Reputation points

2020-09-11T11:31:01.527+00:00

@MelissaMa-msft

B_QT ,B_WEight from bigbalprd data is coming correct,but D_QTY,D_Weight From Dispatch_BD,DispatchBM table data is not coming correct,

if i am execute

DECLARE @StartDate date = '09-01-2020'; DECLARE @enddate date = '09-01-2020'; select a.codeitem,upper(a.Descriptionitem) Descriptionitem, (d.entrydate) D_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,d.entrydate,105) between @startdate and @enddate and a.Packsize='Bigbale' group by a.codeitem,a.Descriptionitem,d.entrydate

then data of Dispatch_BD and DispatchBM is coming correct ,after pivoting ,data is not coming correct
MelissaMa-msft 24,241 Reputation points Moderator

2020-09-14T08:57:49.797+00:00

Hi @Analyst_SQL ,

I found that there was no modification on above your mentioned part in this case.

Actually I could not download your datafile due to some issues.

You would be better to provide more sample data and expected output so that I could proceed with checking. Thanks.

Best regards
Melissa

Answer 2

Hi @Analyst_SQL ,

Please have another try with below:

DECLARE @StartDate  date = '03-06-2020';  
   DECLARE @enddate date = '06-06-2020';  
              
;with cte as (  
              
    select  c.CName category ,a.codeitem,upper(a.Descriptionitem) Descriptionitem,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight  
    from ItemMasterFile a  
    left join Bigbalprd b   
    on a.codeitem=b.codeitem  
    inner join Catagory c   
    on c.cid=isnull(b.CID,a.cid)  
    where convert(date,b.EntryDate,105) between @startdate and @enddate or b.entrydate is null  
    group by a.codeitem,b.entrydate,c.CName,a.Descriptionitem  
    )  
   ,cte1 as (  
   select a.codeitem,upper(a.Descriptionitem) Descriptionitem,  (d.date) D_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,d.date,105) between @startdate and @enddate and a.Packsize='Bigbale'    
   group by a.codeitem,a.Descriptionitem,d.date  
   )  
         
   select a.category, a.codeitem,upper(a.Descriptionitem) Descriptionitem,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  
   into #t  
   from  cte a  
   left join ItemMasterFile c on a.codeitem=c.codeitem  
   left join cte1 b on a.codeitem=b.codeitem  
   and a.B_Date=b.D_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 @SUM1 NVARCHAR (MAX)  
              
   SET @SUM1=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM1=LEFT(@SUM1,LEN(@SUM1)-1)  
              
   DECLARE @SUM2 NVARCHAR (MAX)  
              
   SET @SUM2=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM2=LEFT(@SUM2,LEN(@SUM2)-1)  
              
   DECLARE @SUM3 NVARCHAR (MAX)  
              
   SET @SUM3=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM3=LEFT(@SUM3,LEN(@SUM3)-1)  
              
   DECLARE @SUM4 NVARCHAR (MAX)  
              
   SET @SUM4=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) +'  
                   from #t where ISNULL(B_date,'')<>'' for xml path(''))  
              
   SET @SUM4=LEFT(@SUM4,LEN(@SUM4)-1)  
              
   DECLARE @SUM NVARCHAR (MAX)  
              
   SET @SUM='CAST(SUM('+@SUM1+')-SUM('+@SUM2+') AS VARCHAR),CAST(SUM('+@SUM3+')-SUM('+@SUM4+')AS VARCHAR),'  
              
   DECLARE @query NVARCHAR(MAX)  
   SET @query = '    
              
   select '''' category,'''' codeitem,'''' Descriptionitem,'''' [Balance Bale],'''' [Balance Lbs.],'+@cols3+'  
   UNION ALL  
   SELECT cast(category as varchar(50)) category,cast(codeitem as varchar(10)) codeitem,Descriptionitem,'+@SUM + @cols1 + '  
               FROM (  
       SELECT category,codeitem,Descriptionitem,  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  
   group by codeitem,Descriptionitem,category,' + @cols   
                          
   EXEC SP_EXECUTESQL @query  
  
  
   drop table #t

Best regards
Melissa

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.

Analyst_SQL 3,576 Reputation points

2020-09-11T08:50:44.607+00:00

Hi @MelissaMa-msft

Now null values are not coming,
but i want to display all item from itemmasterfile where a.Packsize='Bigbale' there is approx 91 item,whether there qty exit or not in Bigbaleprd table and dispatch table.
MelissaMa-msft 24,241 Reputation points Moderator

2020-09-11T08:52:58.567+00:00

Hi @Analyst_SQL ,

Please have a try with below query and check again. Thanks.

Best regards
Melissa

Answer 3

Hi @Analyst_SQL ,

There are two similar threads posted by you.

Please help check whether they are duplicate or different.

If they are duplicate ,please delete one of them if necessary. If they are different, please point out the difference. Thanks in advance.

Please refer below:

drop table if exists #ItemMasterFile  
  drop table if exists #Bigbalprd  
  drop table if exists #DispatchBM  
  drop table if exists #DispatchDB  
   drop table if exists #Catagory  
  drop table if exists #t  
          
  Create table #Catagory (CID int,CName varchar(50))  
  Create table #ItemMasterFile (item_ID int,item_Name varchar(50),CID int)  
  Create table #Bigbalprd (B_ID int,item_ID int,B_QTY int,B_Weight int,B_Date date,CID int)  
  Create table #DispatchBM (D_ID int,Name varchar(50),D_Date date)  
  Create table #DispatchDB (ID int ,D_ID int,item_ID int,D_QTY int,D_Weight int)  
       
  Insert into #Catagory values  
  (1,'Cat1'),  
  (2,'Cat2'),  
  (3,'Cat3'),  
  (4,'Cat4')  
          
  INSERT INTO #ItemMasterFile VALUES  
    (1,'A',1)  
  , (2,'B',1)  
  , (3,'C',2)  
  , (4,'D',3)  
  , (5,'e',3)  
  , (6,'f',2)  
  , (7,'g',3)  
  , (8,'h',4)  
  , (9,'K',4)  
  , (10,'L',4)  
  , (11,'M',4);  
          
          
  INSERT INTO #Bigbalprd VALUES  
  (111,1,1,500,'03-06-2020',1)  
  ,(112,2,1,200,'03-06-2020',1)  
  ,(113,1,1,300,'03-06-2020',1)  
  ,(114,6,1,100,'04-06-2020',2)  
  ,(115,1,1,200,'04-06-2020',1)  
  ,(116,1,1,300,'04-06-2020',1)  
  ,(117,7,1,100,'05-06-2020',3)  
  ,(118,5,1,200,'05-06-2020',3)  
  ,(119,8,1,300,'06-06-2020',4)  
          
  Insert into #DispatchBM Values  
  (1001,'Akhter','03-06-2020')  
  ,(1002,'Irfan','05-06-2020')  
  Insert into #DispatchDB Values  
  (11,1001,1,1,500)  
  ,(12,1001,2,1,200)  
  ,(13,1001,1,1,300)  
  ,(14,1002,7,1,100)  
  ,(15,1002,5,1,200)  
          
  DECLARE @StartDate  date = '03-06-2020';  
  DECLARE @enddate date = '06-06-2020';  
          
  ;with cte as (  
      
  select  c.CName category ,a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight  
  from #ItemMasterFile a  
  left join #Bigbalprd b   
  on a.item_ID=b.item_ID  
  inner join #Catagory c   
  on c.cid=isnull(b.CID,a.cid)  
  where convert(date,B_Date,105) between @startdate and @enddate or B_Date is null  
  group by a.item_ID,a.item_Name,B_Date,c.CName  
  )  
  ,cte1 as (  
  select a.item_ID,upper(a.item_Name) item_Name,  D_Date,sum(D_QTY) D_QTY,sum(D_Weight) D_Weight  
  from #ItemMasterFile a  
  left join #DispatchDB c  
  on c.item_ID=a.item_ID  
  left join #DispatchBM d  
  on d.D_ID=c.D_ID   
  where convert(date,D_Date,105) between @startdate and @enddate  
  group by a.item_ID,a.item_Name,d.D_Date  
  )  
     
  select a.category, c.item_ID,upper(c.item_Name) 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  
  into #t  
  from #ItemMasterFile c   
  left join cte a on a.item_ID=c.item_ID  
  left join cte1 b on a.item_ID=b.item_ID  
  and a.B_Date=b.D_Date  
    
  
  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 @SUM1 NVARCHAR (MAX)  
          
  SET @SUM1=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_QTY],0) +'  
                  from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
  SET @SUM1=LEFT(@SUM1,LEN(@SUM1)-1)  
          
  DECLARE @SUM2 NVARCHAR (MAX)  
          
  SET @SUM2=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_QTY],0) +'  
                  from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
  SET @SUM2=LEFT(@SUM2,LEN(@SUM2)-1)  
          
  DECLARE @SUM3 NVARCHAR (MAX)  
          
  SET @SUM3=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' D_Weight],0) +'  
                  from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
  SET @SUM3=LEFT(@SUM3,LEN(@SUM3)-1)  
          
  DECLARE @SUM4 NVARCHAR (MAX)  
          
  SET @SUM4=(SELECT DISTINCT 'ISNULL([' + CONVERT(NVARCHAR, B_date, 23) + ' B_Weight],0) +'  
                  from #t where ISNULL(B_date,'')<>'' for xml path(''))  
          
  SET @SUM4=LEFT(@SUM4,LEN(@SUM4)-1)  
          
  DECLARE @SUM NVARCHAR (MAX)  
          
  SET @SUM='CAST(SUM('+@SUM1+')-SUM('+@SUM2+') AS VARCHAR),CAST(SUM('+@SUM3+')-SUM('+@SUM4+')AS VARCHAR),'  
          
  DECLARE @query NVARCHAR(MAX)  
  SET @query = '    
          
  select '''' category,'''' item_id,'''' item_Name,'''' [Balance Bale],'''' [Balance Lbs.],'+@cols3+'  
  UNION ALL  
  SELECT cast(category as varchar(10)) category,cast(item_id as varchar(10)) item_id,item_Name,'+@SUM + @cols1 + '  
              FROM (  
      SELECT category,item_ID,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  
  group by item_id,item_Name,category,' + @cols   
                      
  EXEC SP_EXECUTESQL @query

Output:

Best regards
Melissa

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.

Analyst_SQL 3,576 Reputation points

2020-09-10T10:36:24.377+00:00

@Melissa Ferriola

When i executed your provided query in my actual data ,then null value in category are coming,but i checked, that there is no any null CID value in Bigbale table and itemmastertfile table .

below image is attached
MelissaMa-msft 24,241 Reputation points Moderator

2020-09-11T00:52:00.943+00:00

Hi @Analyst_SQL ,

Could you please check whether you used my complete updated query?

Actually I faced the similar issue with you at the beginning.

Later I made it working by adding 'or B_Date is null' in where condition in below part.

;with cte as ( select c.CName category ,a.item_ID,upper(a.item_Name) item_Name,B_Date,sum(B_QTY) B_QTY,sum(B_Weight) B_Weight from #ItemMasterFile a left join #Bigbalprd b on a.item_ID=b.item_ID inner join #Catagory c on c.cid=isnull(b.CID,a.cid) where convert(date,B_Date,105) between @startdate and @enddate or B_Date is null group by a.item_ID,a.item_Name,B_Date,c.CName )

If you still face this issue, it could be better to provide some of your actual data and I could check accordingly since my query is working fine with data you provided.

Thanks for understanding.

Best regards
Melissa
MelissaMa-msft 24,241 Reputation points Moderator

2020-09-11T00:54:27.123+00:00

Hi @Analyst_SQL ,

Please also help check another similar thread you posted and procced with delete or provide more details. Thanks.

Add Column of Category in Pivot query

Best regards
Melissa
Analyst_SQL 3,576 Reputation points

2020-09-11T05:40:16.637+00:00

here i shared link of google drive ,database attached ,also attached modify query according to my field name
please check

view

24041-query1.txt

Answer 4

Hi @Analyst_SQL ,

To troubleshoot this issue, please provide the output or snapshot of below two quries with your actual data.

Query 1：

DECLARE @StartDate  date = '03-06-2020';  
   DECLARE @enddate date = '06-06-2020';  
              
;with cte as (  
              
    select  c.CName category ,a.codeitem,upper(a.Descriptionitem) Descriptionitem,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight  
    from ItemMasterFile a  
    left join Bigbalprd b   
    on a.codeitem=b.codeitem  
    inner join Catagory c   
    on c.cid=isnull(b.CID,a.cid)  
    where convert(date,b.EntryDate,105) between @startdate and @enddate or b.entrydate is null  
    group by a.codeitem,b.entrydate,c.CName,a.Descriptionitem  
    )  
  
 select * from cte

Query 2：

 DECLARE @StartDate  date = '03-06-2020';  
   DECLARE @enddate date = '06-06-2020';  
              
;with cte as (  
              
    select  c.CName category ,a.codeitem,upper(a.Descriptionitem) Descriptionitem,(b.EntryDate)B_Date,sum(Bpqty) B_QTY,sum(Bweight) B_Weight  
    from ItemMasterFile a  
    left join Bigbalprd b   
    on a.codeitem=b.codeitem  
    inner join Catagory c   
    on c.cid=isnull(b.CID,a.cid)  
    where convert(date,b.EntryDate,105) between @startdate and @enddate or b.entrydate is null  
    group by a.codeitem,b.entrydate,c.CName,a.Descriptionitem  
    )  
   ,cte1 as (  
   select a.codeitem,upper(a.Descriptionitem) Descriptionitem,  (d.date) D_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,d.date,105) between @startdate and @enddate and a.Packsize='Bigbale'    
   group by a.codeitem,a.Descriptionitem,d.date  
   )  
         
   select a.category, c.codeitem,upper(c.Descriptionitem) Descriptionitem,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  
   from ItemMasterFile c   
   left join cte a on a.codeitem=c.codeitem  
   left join cte1 b on a.codeitem=b.codeitem  
   and a.B_Date=b.D_Date  
    where c.Packsize='Bigbale'

Best regards
Melissa

Analyst_SQL 3,576 Reputation points

2020-09-11T06:46:55.34+00:00

Query 1 output null is not coming,

Query 2 output,null are coming

in screenshot ,still null values are coming
MelissaMa-msft 24,241 Reputation points Moderator

2020-09-11T08:46:47.467+00:00

Hi @Analyst_SQL ,

Please find both of two queries from below and check whether one of them is working with your actual data.

If both of them are not working, please provide the output of above Query 2 and complete query. Thanks.

Best regards
Melissa

Answer 5

MelissaMa-msft 24,241 Moderator

Hi @Analyst_SQL ,

Or you could try with below part:

select isnull(a.category,d.CName) category, c.codeitem,upper(c.Descriptionitem) Descriptionitem,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  
    into #t  
    from ItemMasterFile c   
	inner join  Catagory d on d.cid=c.CID  
    left join cte a on a.codeitem=c.codeitem  
    left join cte1 b on a.codeitem=b.codeitem  
    and a.B_Date=b.D_Date  
     where c.Packsize='Bigbale'

Best regards
Melissa

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.

Analyst_SQL 3,576 Reputation points

2020-09-11T13:41:30.58+00:00

hi @MelissaMa-msft

are you understood ,that i am trying to make you understand.

Share via

add category column ,in pivot query.

4 additional answers

Your answer