Share via

Add Column of Category in Pivot query

Analyst_SQL 3,576 Reputation points
2020-09-10T08:19:30.587+00:00

hi @MelissaMa-msft

i have data below with query,i want category value get from #Bigbaleprd table, if CID column id exit ,otherwise it get from item master file, 

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))  
 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

23781-cat.jpg

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-msft 24,241 Reputation points Moderator
    2020-09-10T09:05:32.663+00:00

    Hi @Analyst_SQL ,

    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:
    23650-cat.png

    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.

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.