Table self-join for derived columns

• pwalter83

  SSChampion

  Points: 14575

  More actions

  February 26, 2020 at 11:58 am

  Go to Answer

  #3728848

  Hi,

  I have a requirement to derive additional columns and populate the values by doing a self-join.

  I have tried to use self-join and cross apply but not getting the desired results.

  I have attached the desired result format and also included the DDL and sample data.

  Could somebody please help in this regard. Thanks.

  CREATE TABLE [dbo].[Sample1](
  [Column 0] [varchar](50) NULL,
  [Column 1] [varchar](50) NULL,
  [Column 2] [varchar](50) NULL,
  [Column 3] [varchar](50) NULL,
  [Column 4] [varchar](50) NULL
  ) ON [PRIMARY]
  
  INSERT INTO [dbo].[Sample]
  ([Column 0],[Column 1],[Column 2],[Column 3],[Column 4])
       
  VALUES('PAYMENTFILE','250','42','20200224','1330')
  
  INSERT INTO [dbo].[Sample]
  ([Column 0],[Column 1],[Column 2],[Column 3],[Column 4])
  
  VALUES('1','1003003382','GBP','171.95','20200224')
  
  INSERT INTO [dbo].[Sample]
  ([Column 0],[Column 1],[Column 2],[Column 3],[Column 4])
  
  VALUES('2','1003003383','GBP','171.95','20200224')
  
  INSERT INTO [dbo].[Sample]
  ([Column 0],[Column 1],[Column 2],[Column 3],[Column 4])
  
  VALUES('3','1003003384','GBP','171.95','20200224')

   

   

   

  Attachments:

  You must be logged in to view attached files.

• Steve Collins

  SSCrazy

  Points: 2227

  More actions

  February 26, 2020 at 1:07 pm

  Answer

  #3728868

  It appears you have two different sets of information shoe-horned into the same table.  Maybe the information was derived from base tables which are more straightforward to query?  How is this table being populated?  CROSS JOIN would work.

  ;with
  pay_cte as (select * from Sample1 where [Column 0]='PAYMENTFILE'),
  int_cte as (select * from Sample1 where [Column 0]<>'PAYMENTFILE')
  select
    pc.[Column 1] Payment_code,
    ic.[Column 0] Line_ID,
    pc.[Column 2] Batch_Number,
    ic.[Column 1] External_Order_ID,
    ic.[Column 3] Gross_Value,
    ic.[Column 2] Currency,
    pc.[Column 3] File_Date,
    pc.[Column 4] File_Time,
    convert(varchar, cast(ic.[Column 4] as date), 103) Date_Created
  from
    pay_cte pc
   cross join
    int_cte ic;

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• pwalter83

  SSChampion

  Points: 14575

  More actions

  February 26, 2020 at 3:13 pm

  #3728939

  Steve Collins wrote:

  It appears you have two different sets of information shoe-horned into the same table.  Maybe the information was derived from base tables which are more straightforward to query?  How is this table being populated?  CROSS JOIN would work.

  ;with
  pay_cte as (select * from Sample1 where [Column 0]='PAYMENTFILE'),
  int_cte as (select * from Sample1 where [Column 0]<>'PAYMENTFILE')
  select
    pc.[Column 1] Payment_code,
    ic.[Column 0] Line_ID,
    pc.[Column 2] Batch_Number,
    ic.[Column 1] External_Order_ID,
    ic.[Column 3] Gross_Value,
    ic.[Column 2] Currency,
    pc.[Column 3] File_Date,
    pc.[Column 4] File_Time,
    convert(varchar, cast(ic.[Column 4] as date), 103) Date_Created
  from
    pay_cte pc
   cross join
    int_cte ic;

  Thanks very much for your helpful reply. It worked perfectly !

  The table itself is actually a dump of a delimited text file. The original requirement is to import delimited data from the text file to the SQL table which should be in the above format.

  Thanks again.

   

  • This reply was modified 5 years, 4 months ago by pwalter83.