Table self-join for derived columns

Question

Post reply

Table self-join for derived columns

pwalter83

SSChampion

Points: 14557

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

Default port

Points: 1468

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

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

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

pwalter83 SSChampion Points: 14557 More actions · Answer 1

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 1 year, 6 months ago by pwalter83.