How to concatenate some elements in sql

Question

I got a problem in SQL,

I did a lot of request and now I want concatenate my first and last line on some specifics elements.

Here a perfect example:


id   enter_date     exit_date    money 

1      02/02/2020    28/02/2020    200$
1      28/02/2020    28/02/2020    220$
1      28/02/2020    04/05/2020    250$  

2      12/08/2020    17/12/2020    500$ 
2      17/12/2020    .             700$

And my aim is to got that :

id     enter_date     exit_date    money 

1      02/02/2020   04/05/2020     250$

2      12/08/2020    .             700$

So like you can see, I took the enter_date from the first line and I took all the others elements from the last line ( except the enter_date). I want concatenate my first line with my last line

Please provide what RDMS you are using, your actual table structure, and what you have tried so far. — Brad, Jun 29 '21 at 12:55
It's not clear how you are ordering your rows and what is the data type of your *date* columns? `.` is not a valid date so that suggests a string - and if the dates are the same over multiple rows as with `id 1` then how do you determine the first or last row? — Stu, Jun 29 '21 at 13:49
Since this is in SAS, does this *have* to be done in PROC SQL? Because there are a lot of SAS tools you can use to achieve this. — Stu Sztukowski, Jun 30 '21 at 14:01
Do you have to worry about gaps in the dates for any ID's? If not then you can just use MIN(), MAX() and SUM() aggregate functions of SQL and GROUP BY the ID variables. — Tom, Jun 30 '21 at 14:30

Gabriel Durac · Answer 1 · 2021-06-30T09:56:04.690

0

You can use window functions if they are available in your RDBMS.

SELECT DISTINCT
id
,FIRST_VALUE(enter_date) OVER (PARTITION BY id ORDER BY enter_date, exit_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
,LAST_VALUE(exit_date) OVER (PARTITION BY id ORDER BY enter_date, exit_date  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
,LAST_VALUE(money) OVER (PARTITION BY id ORDER BY enter_date, exit_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM YourTable

As long as you don't have duplicate for id,exit_date pairs you could also use:

select AGG_TABLE.id
,AGG_TABLE.min_enter_date as enter_date
,AGG_TABLE.max_exit_date as  exit_date
,SOURCE_TABLE.money
FROM (
   SELECT id
     ,MIN(enter_date) as min_enter_date
     ,MAX(exit_date) as max_exit_date
   FROM YourTable
   GROUP BY id ) as AGG_TABLE
INNER JOIN YourTable as SOURCE_TABLE 
    ON SOURCE_TABLE.id=AGG_TABLE.id 
    AND AGG_TABLE.max_exit_date=SOURCE_TABLE.exit_date

edited Jun 30 '21 at 09:56

answered Jun 29 '21 at 13:14

Gabriel Durac

2,610
1
12
13

Hello, thanks, but I tried it and it doesn't work, I use proc sql on sas – seb Jun 30 '21 at 09:38
the syntax i used is T-SQL. From what I see PROC SQL does not support window functions. I added the SAS and PROC SQL tags to your question so you get better answers. – Gabriel Durac Jun 30 '21 at 09:46
I also provided a version without window functions, however it will only work if you do not have duplicate for id,exit_date pairs. – Gabriel Durac Jun 30 '21 at 09:57
thx ! but What do you mean per duplicate id ? And to be honest, I don' unterstand your function – seb Jun 30 '21 at 10:10

Stu Sztukowski · Accepted Answer · 2021-06-30T14:25:44.513

Since this is in SAS, a data step would be a more effective option than SQL if the processing is being done on the SAS server. You can do this with by-group processing, the retain statement, and first. and last. logic.

data want;
    set have;
    by id enter_date;

    /* Do not reset this value at the run boundary */
    retain first_enter_date;

    /* For the first ID in the group, store the enter date */
    if(first.id) then first_enter_date = enter_date;

    /* If it's the last ID, set enter_date to the 
       stored enter date and output only the last row */
    if(last.id) then do;
        enter_date = first_enter_date;
        output;
    end;

    drop first_enter_date;
run;

Output:

id  enter_date  exit_date   money
1   02/02/2020  04/05/2020  $250
2   12/08/2020  .           $700

How to concatenate some elements in sql

2 Answers2