question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft commented

Split data from one column into two column.

I am trying to splitting one column data into two column,Means Data Floor One(F1_QTY,F1_Weight) and Floor Two(F2_QTY,F2_Weight),Currently getting result on Floor One ,but Floor Two(F2_QTY,F2_Weight) is coming null.
I also want to display all items.


Dumpy Data

 Create table #Section (SecID int,SecName varchar(50))
 Create table #Categoory(CID int,CName varchar(50))
 Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50),CID int,SecId int)
   Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,delid int,Secid int,CID int,FID int)
    
   INSERT INTO #Section VALUES
     (11,'S1')
   , (22,'S2')
   , (33,'S3')
   , (44,'S4')
   , (55,'S5')
 ;
   INSERT INTO #Categoory VALUES
    (1,'C1')
   , (2,'C2')
   , (3,'C3')
   , (4,'C4')
   , (5,'C5')
 ;
    
    
            
   INSERT INTO #ItemMasterFile VALUES
     (1,'A','Bigbale',11,1)
   , (2,'B','Bigbale',22,2)
   , (3,'C','Bigbale',33,1)
   , (4,'D','Bigbale',33,1)
   , (5,'e','Bigbale',44,1)
   , (6,'f','Bigbale',55,4)
   , (7,'g','Bigbale',11,4)
   , (8,'h','Bigbale',55,2)
   , (9,'K','Bigbale',55,3)
   , (10,'L','Bigbale',44,3)
   , (11,'M','Bigbale',22,3);
            
            
   INSERT INTO #Bigbalprd VALUES
   (111,1,1,500,'04-06-2020',11,1,1,null)
   ,(112,2,1,200,'04-06-2020',22,2,2,null)
   ,(113,1,1,300,'04-06-2020',33,11,1,null)
   ,(114,6,1,100,'04-06-2020',55,4,1,null)
   ,(115,1,1,200,'04-06-2020',11,1,1,null)
   ,(116,1,1,300,'04-06-2020',11,1,1,null)
   ,(117,7,1,100,'04-06-2020',11,4,1,null)
   ,(118,5,1,200,'04-06-2020',44,1,2,null)
   ,(119,8,1,300,'04-06-2020',55,2,2,null)
            



 ;with 
 cte2 as (SELECT Catagory.CName as Catagory ,ItemMasterFile.Descriptionitem as Artical, F.FName as F1,
 Sum(Bigbalprd.Bpqty) as F1_QTY,Sum(Bigbalprd.Bweight) as F1_Weight 
     FROM         Bigbalprd INNER JOIN 
    
                          ItemMasterFile ON Bigbalprd.CodeItem = ItemMasterFile.CodeItem  INNER JOIN 
                          Catagory ON Catagory.CID = Bigbalprd.CID
     inner join Floor F on F.FID=Bigbalprd.FID
                         and Bigbalprd.Entrydate between '2021-04-01' and '2021-04-01' and Bigbalprd.delID is null and F.FID=1
    group by ItemMasterFile.Descriptionitem,Catagory.CName,F.FName) 
    
 ,cte3 as (SELECT Catagory.CName as Catagory ,ItemMasterFile.Descriptionitem as Artical, F.FName as F2,
 Sum(Bigbalprd.Bpqty) as F2_QTY,Sum(Bigbalprd.Bweight) as F2_Weight
     FROM         Bigbalprd INNER JOIN 
    
                          ItemMasterFile ON Bigbalprd.CodeItem = ItemMasterFile.CodeItem  INNER JOIN 
                          Catagory ON Catagory.CID = Bigbalprd.CID
     inner join Floor F on F.FID=Bigbalprd.FID
                         and Bigbalprd.Entrydate between '2021-04-02' and '2021-04-02' and Bigbalprd.delID is null and F.FID=2
    group by ItemMasterFile.Descriptionitem,Catagory.CName,F.FName)
    
    select   cte2.Catagory as Category,cte2.Artical, F1_QTY,F1_Weight,F2_QTY,F2_Weight from cte2 left join cte3 on 
    cte3.F2=cte2.F1 

88095-image.png




sql-server-generalsql-server-transact-sql
image.png (45.9 KiB)
· 10
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @akhterhussain-3167,

I read your code and found no obvious errors.Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.

The null value is returned because there is no FName in cte3 that matches cte2. Only when the same Fname contains data in two layers at the same time, that is, when cte2 and cte3 have the same Fname, Floor Two (F2_QTY, F2_Weight) will not be empty.

Echo

0 Votes 0 ·

I have given dummy Data above.

0 Votes 0 ·

Does the #Section table represent the Floor table?
Sorry, there are some differences in the sample data query you provided. I tried to modify it but couldn't get started.

0 Votes 0 ·
Show more comments

Hi. Where is your Floor table and example data ? Where is your section table in the script?

0 Votes 0 ·
 Create table #Floor (FID int,Fname Varchar(50))
 INSERT INTO #Floor VALUES
 (1,'FIrst')
 , (2,'Second');

0 Votes 0 ·

What do you expected for your sample data? Please add your expected return for this data.

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Your code returns the correct result. So what is the output you expect?
Your dummy data can only return null values, so I modified the test data:

  Create table #Categoory(CID int,CName varchar(50))
  Create table #ItemMasterFile (Codeitem int,Descriptionitem varchar(50),Packsize varchar(50),CID int,SecId int)
    Create table #Bigbalprd (B_ID int,Codeitem int,Bpqty int,Bweight int,Entrydate date,delid int,Secid int,CID int,FID int)
     
  Create table #Floor (FID int,Fname Varchar(50))
  INSERT INTO #Floor VALUES
  (1,'FIrst') 
  , (2,'Second'),
  (1,'Second')
    
    INSERT INTO #Categoory VALUES
     (1,'C1')
    , (2,'C2')
    , (1,'C3')
    , (2,'C4')
    , (1,'C5');
        
 INSERT INTO #ItemMasterFile VALUES
      (1,'A','Bigbale',11,1)
    , (2,'B','Bigbale',22,2)
    , (3,'C','Bigbale',33,1)
    , (4,'D','Bigbale',33,1)
    , (5,'e','Bigbale',44,1)
    , (6,'f','Bigbale',55,4)
    , (7,'g','Bigbale',11,4)
    , (8,'h','Bigbale',55,2)
    , (9,'K','Bigbale',55,3)
    , (10,'L','Bigbale',44,3)
    , (11,'M','Bigbale',22,3);
                
                
    INSERT INTO #Bigbalprd VALUES
    (111,1,1,500,'04-01-2021',11,1,1,1)
    ,(112,2,1,200,'04-02-2021',null,2,2,2)
    ,(113,1,1,300,'04-01-2021',33,11,1,1)
    ,(114,6,1,100,'04-02-2021',55,4,1,2)
    ,(115,1,1,200,'04-01-2021',null,1,1,1)
    ,(116,1,1,300,'04-02-2021',11,1,1,1)
    ,(117,7,1,100,'04-01-2021',11,4,1,2)
    ,(118,5,1,200,'04-02-2021',null,1,2,2)
    ,(119,8,1,300,'04-01-2021',null,2,2,1)
    
 select * from #Bigbalprd
 select * from #ItemMasterFile
 select * from #Categoory 
 select * from #Floor
    
                
  ;with 
  cte2 as (SELECT #Categoory.CName as Catagory ,#ItemMasterFile.Descriptionitem as Artical, F.FName as F1,
  Sum(#Bigbalprd.Bpqty) as F1_QTY,Sum(#Bigbalprd.Bweight) as F1_Weight 
  FROM  #Bigbalprd
  INNER JOIN #ItemMasterFile 
  ON #Bigbalprd.CodeItem = #ItemMasterFile.CodeItem  
  INNER JOIN #Categoory ON #Categoory.CID = #Bigbalprd.CID
  inner join #Floor F 
  on F.FID=#Bigbalprd.FID and #Bigbalprd.Entrydate between '2021-04-01' and '2021-04-01' and #Bigbalprd.delID is null and F.FID=1
 group by #ItemMasterFile.Descriptionitem,#Categoory.CName,F.FName) 
        
  ,cte3 as (SELECT #Categoory.CName as Catagory ,#ItemMasterFile.Descriptionitem as Artical, F.FName as F2,
  Sum(#Bigbalprd.Bpqty) as F2_QTY,Sum(#Bigbalprd.Bweight) as F2_Weight
  FROM  #Bigbalprd 
  INNER JOIN #ItemMasterFile 
  ON #Bigbalprd.CodeItem = #ItemMasterFile.CodeItem  
  INNER JOIN #Categoory ON #Categoory.CID = #Bigbalprd.CID
 inner join #Floor F 
 on F.FID=#Bigbalprd.FID and #Bigbalprd.Entrydate between '2021-04-02' and '2021-04-02' and #Bigbalprd.delID is null and F.FID=2
 group by #ItemMasterFile.Descriptionitem,#Categoory.CName,F.FName)
        
 select   cte2.Catagory as Category,cte2.Artical, F1_QTY,F1_Weight,F2_QTY,F2_Weight 
 from cte2 
 left join cte3 on cte3.F2=cte2.F1 

Output:
88146-image.png
What matters is what kind of result you want, and what your data structure looks like.If we have this information, we can avoid endless games of riddles.

Regards
Echo



image.png (19.4 KiB)
image.png (19.6 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

below is expected Output.
88159-image.png


0 Votes 0 ·
image.png (16.8 KiB)

Gosh, I'm totally lost. If you can't provide sample data, the problem will not be solved, I really can't guess what your data looks like.

0 Votes 0 ·

Hi Echoliu,
Please forget query which i provided above, I need your query, Data i provided above, result which i provided for you is in iamge

0 Votes 0 ·
Show more comments