Post reply

Table self-join for derived columns

  • pwalter83

    SSChampion

    Points: 14548

    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.

  • scdecade

    Say Hey Kid

    Points: 693

    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: 14548

    February 26, 2020 at 3:13 pm

    #3728939

    scdecade 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 1 month ago by  pwalter83.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply