none
How to fill a column with values from a different column? RRS feed

  • Question

  • I'm not really sure how to ask this .What I want to do is for every fjobono I want to the factqty to autofill into a new column called Resin. I'm using ResRank to identify which value I want to fill the new column. I'm using the Row_Number function to achieve this. The problem I'm having is specifying I want the factqty to be entered into the new column where ResRank also >= 2

       fjobono    |     fbompart  |                          fbomdesc            |factqty |  ResRank |  fbommeas  | fprodcl

    IR525-0000 ANF-37-00000   HOUSE ACETAL                                0.03570       1            LBS           20
    IR525-0000 ANF-48-00017   BLUE ACC #75688                           0.00107      2           LBS             21
    IR528-0000 ANF-31-00000    HOUSE HIPS- HIGH IMPACT              0.37540      1          LBS             20
    IR528-0000 ANF-48-00016     BLUE ACC #75689                          0.01130        2         LBS             21

    This is what I want the data to look like

       fjobono    |     fbompart  |                          fbomdesc            |factqty   |  Resin       |  ResRank |  fbommeas  | fprodcl

    IR525-0000 ANF-37-00000   HOUSE ACETAL                                0.03570 |0.03570       1            LBS               20
    IR525-0000 ANF-48-00017   BLUE ACC #75688                           0.00107 |0.03570         2           LBS              21
    IR528-0000 ANF-31-00000    HOUSE HIPS- HIGH IMPACT              0.37540 |0.37540       1            LBS             20
    IR528-0000 ANF-48-00016     BLUE ACC #75689                          0.01130| 0.37540        2             LBS             21

    SELECT   jodbom.fjobno , jodbom.fbompart , jodbom.fbomdesc, jodbom.factqty ,
    Row_Number () OVER (Partition by jodbom.fjobno order by jodbom.factqty desc)   as ResRank,
    
     jodbom.fbommeas, inmastx.fprodcl
    FROM       jodbom INNER JOIN
            inmastx ON jodbom.fbompart + jodbom.fbomrev = inmastx.fpartno + inmastx.frev
    WHERE   (inmastx.fprodcl = '20' OR
        inmastx.fprodcl = '21') AND (jodbom.fjobno >= 'IR525-0000')

    Friday, October 19, 2018 2:45 PM

Answers

  • CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
     ;with mycte as (
    select fjobono,fbompart,fbomdesc,factqty
    , row_number() Over(Partition by fjobono Order by ResRank) as rn,ResRank,fbommeas,fprodcl       from   mytable
    )
    
    select fjobono,fbompart,fbomdesc,factqty,Resin,ResRank,fbommeas,fprodcl  
    from mycte m
    cross apply (select factqty as Resin from mycte m2 where m2.rn=1 and m.fjobono=m2.fjobono) d(Resin)
    
    
    drop TABLE mytable

    • Marked as answer by David9501 Tuesday, October 23, 2018 3:45 PM
    Friday, October 19, 2018 3:16 PM
    Moderator

All replies

  • CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
    select fjobono,fbompart,fbomdesc,factqty
    , first_value(factqty) Over(Partition by fjobono Order by ResRank) as Resin,ResRank,fbommeas,fprodcl       from   mytable
    
    
    drop TABLE mytable

    Friday, October 19, 2018 2:58 PM
    Moderator
  • CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
    select fjobono,fbompart,fbomdesc,factqty
    , first_value(factqty) Over(Partition by fjobono Order by ResRank) as Resin,ResRank,fbommeas,fprodcl       from   mytable
    
    
    drop TABLE mytable


    I'm currently on SQL Server 2008 R2 so I'm not able to use the First_value function.
    Friday, October 19, 2018 3:07 PM
  • CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
     ;with mycte as (
    select fjobono,fbompart,fbomdesc,factqty
    , row_number() Over(Partition by fjobono Order by ResRank) as rn,ResRank,fbommeas,fprodcl       from   mytable
    )
    
    select fjobono,fbompart,fbomdesc,factqty,Resin,ResRank,fbommeas,fprodcl  
    from mycte m
    cross apply (select factqty as Resin from mycte m2 where m2.rn=1 and m.fjobono=m2.fjobono) d(Resin)
    
    
    drop TABLE mytable

    • Marked as answer by David9501 Tuesday, October 23, 2018 3:45 PM
    Friday, October 19, 2018 3:16 PM
    Moderator
  • --or

    CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
     
    select fjobono,fbompart,fbomdesc,factqty
    ,Resin,ResRank,fbommeas,fprodcl       
    from   mytable m
    cross apply (select top 1 factqty as Resin from mytable m2 where   m.fjobono=m2.fjobono order by ResRank ) d(Resin)
    
    drop TABLE mytable

    Friday, October 19, 2018 7:44 PM
    Moderator
  • Hi David9501,

    Per your description, you would like to make the value of Resin be the first value under the grouping of the column 'fjobono'. Right?   

     

    Please try following script to see if it satisfies your requirement.

    ---drop table mytable
    CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas,fprodcl) VALUES 
     ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', 'ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', 'ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', 'ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
     
     ;with cte as(
     select *, 1 as first_value  from mytable
     )
     select b.fjobono,b.fbompart,b.fbomdesc,b.factqty,a.factqty as Resin,b.ResRank,b.fbommeas,b.fprodcl 
     from mytable a 
     join cte b on a.fjobono=b.fjobono and a.ResRank=b.first_value
     /*
     fjobono                                                                                                           fbompart                                                                                             fbomdesc                                                                                             factqty                                                                                              Resin                                                                                                ResRank                                                                                              fbommeas                                                                                             fprodcl
    ----------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
    IR525-0000                                                                                                        ANF-37-00000                                                                                         HOUSE ACETAL                                                                                         0.03570                                                                                              0.03570                                                                                              1                                                                                                    LBS                                                                                                  20
    IR525-0000                                                                                                        ANF-48-00017                                                                                         BLUE ACC #75688                                                                                      0.00107                                                                                              0.03570                                                                                              2                                                                                                    LBS                                                                                                  21
    IR528-0000                                                                                                        ANF-31-00000                                                                                         HOUSE HIPS- HIGH IMPACT                                                                              0.37540                                                                                              0.37540                                                                                              1                                                                                                    LBS                                                                                                  20
    IR528-0000                                                                                                        ANF-48-00016                                                                                         BLUE ACC #75689                                                                                      0.01130                                                                                              0.37540                                                                                              2                                                                                                    LBS                                                                                                  21
     */
    


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 22, 2018 6:22 AM
  • CREATE TABLE mytable(
       fjobono  VARCHAR(20) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  decimal(10,5)
      ,ResRank int
      ,fbommeas VARCHAR(100)
      ,fprodcl  int
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
     Select  fjobono,fbompart,fbomdesc,factqty,
     Cast(Substring( MAX(CAST(fjobono AS BINARY(4))+Cast(Case when ResRank =1 then factqty else null end    as  BINARY(8)) ) 
            OVER( partition by fjobono ORDER BY fjobono,ResRank ROWS UNBOUNDED PRECEDING ),5,8)  as decimal(10,5))    AS Resin
     ,ResRank,fbommeas,fprodcl
     from mytable
     
    
    drop TABLE mytable

    Monday, October 22, 2018 1:44 PM
    Moderator
  • DECLARE @T TABLE (
    	fjobono  VARCHAR(113) NOT NULL,
    	fbompart VARCHAR(100),
    	fbomdesc VARCHAR(100),
    	factqty  VARCHAR(100),
    	fbommeas VARCHAR(100),
    	fprodcl  VARCHAR(100)
    );
    
    INSERT INTO @T VALUES
    ('IR525-0000', 'ANF-37-00000', 'HOUSE ACETAL', 0.03570, 'LBS',  20),
    ('IR525-0000', 'ANF-48-00017', 'BLUE ACC #75688', 0.00107, 'LBS', 21),
    ('IR528-0000', 'ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT', 0.37540, 'LBS', 20),
    ('IR528-0000', 'ANF-48-00016', 'BLUE ACC #75689', 0.01130, 'LBS', 21);
    
    WITH CTE AS (
    	SELECT fjobono, MAX(factqty) AS factqty, COUNT(fjobono) AS ResRank
    	FROM @T
    	GROUP BY fjobono
    )
    
    SELECT  t1.fjobono, 
    		t1.fbompart, 
    		t1.fbomdesc, 
    		t1.factqty, 
    		CASE WHEN t2.ResRank >= 2 THEN t2.factqty ELSE NULL END AS Resin,
    		t1.fbommeas, 
    		t1.fprodcl
    FROM @T AS t1
    LEFT JOIN CTE AS t2 ON t2.fjobono = t1.fjobono;


    A Fan of SSIS, SSRS and SSAS

    Monday, October 22, 2018 2:47 PM
  • CREATE TABLE mytable(
       fjobono  VARCHAR(113) NOT NULL  
      ,fbompart VARCHAR(100)
      ,fbomdesc VARCHAR(100)
      ,factqty  VARCHAR(100)
      ,ResRank  VARCHAR(100)
      ,fbommeas VARCHAR(100)
      ,fprodcl  VARCHAR(100)
    );
    INSERT INTO mytable(fjobono,fbompart,fbomdesc,factqty,ResRank,fbommeas
    ,fprodcl) VALUES ('IR525-0000', 'ANF-37-00000',   'HOUSE ACETAL', 0.03570 , 1 , 'LBS',  20 ),
     ('IR525-0000', ' ANF-48-00017', 'BLUE ACC #75688' , 0.00107 , 2 , 'LBS', 21 ),
     ('IR528-0000', ' ANF-31-00000', 'HOUSE HIPS- HIGH IMPACT' , 0.37540 , 1 , 'LBS', 20),
     ('IR528-0000', ' ANF-48-00016', 'BLUE ACC #75689' , 0.01130 , 2 , 'LBS', 21 );
    
     ;with mycte as (
    select fjobono,fbompart,fbomdesc,factqty
    , row_number() Over(Partition by fjobono Order by ResRank) as rn,ResRank,fbommeas,fprodcl       from   mytable
    )
    
    select fjobono,fbompart,fbomdesc,factqty,Resin,ResRank,fbommeas,fprodcl  
    from mycte m
    cross apply (select factqty as Resin from mycte m2 where m2.rn=1 and m.fjobono=m2.fjobono) d(Resin)
    
    
    drop TABLE mytable


    This worked great with a few modifications. Thank you.
    Tuesday, October 23, 2018 3:45 PM