Many rows to one row

Database
Enthusiast

Many rows to one row

Dear all,

 

i have a little issue. I started with learning SQL but in that issue I can' find a solution.

 

I have to tables like these below

Table1: (only ColumnA is needed)

ColumnA      SDate                 

AA123          25.12.2016 

AB456          25.12.2016

CC789          25.12.2016 

 

Table2:

ColumnA     Stat          scan_trk_tmstp             loc_cd     scan_post_tmstp

AA123          20            27.12.2016  xx:xx         HGZ       27.12.2016  xx:xx

AB456          20            28.12.2016  xx:xx         HGZ       29.12.2016  xx:xx

CC789         20              26.12.2016  xx:xx        KLO       27.12.2016  xx:xx

AA123          65             27.12.2016  xx:xx         HGZ       27.12.2016  xx:xx

AA123          65             28.12.2016  xx:xx         HGZ       29.12.2016  xx:xx

AB456          50             28.12.2016  xx:xx        HGZ       28.12.2016  xx:xx

AB456          65             28.12.2016  xx:xx        HGZ       29.12.2016  xx:xx

 

 

SELECT

c.ColumnA,

c.SDate,

c.LastStat65,

c.LATE

 

from(

SELECT

a.ColumnA,

a.SDate,

SUBSTR(b.loc_cd,1,3) as ScanLoc,

CASE WHEN b.Stat IN ('65') then max(CAST(CAST(b.scan_trk_tmstp AS FORMAT 'YYYYMMDD') AS CHAR(8))) over (partition by ScanLoc, a.ColumnA) END AS LastStat65,

CASE WHEN b.Stat IN ('20') and (EXTRACT(DAY FROM b.scan_post_tmstp)) <> (EXTRACT(DAY FROM b.scan_trk_tmstp)) THENcast('Late' as CHAR(8)) END AS LATE

 

FROM Table1 a

LEFT JOIN Table2 b

on a.ColumnA = b.ColumnA

where b.loc_cd like any ('HGZ%','KLO%')

 )c

group by 1,2,3,4;

 

The result is something like that

ColumnA     SDate            LastStat65                  LATE

AA123          25.12.2016    28.12.2016  xx:xx       -

AA123          25.12.2016    -                                 Late

AA123          25.12.2016    -                                 -

AB456          25.12.2016   28.12.2016  xx:xx       -

AB456          25.12.2016    -                                 Late

AB456          25.12.2016    -                                 -

CC789          25.12.2016    -                                 Late

CC789          25.12.2016    -                                 - 

 

but what I need is that

AA123          25.12.2016    28.12.2016  xx:xx      Late

 

AB456          25.12.2016   28.12.2016  xx:xx       Late

 

CC789          25.12.2016    -                                 Late

 

I dont't know if this is possible on a easy way or if I have a blackout in my brain.


Hopefully regards

Sven 

 

2 REPLIES
Teradata Employee

Re: Many rows to one row

Try using aggregates, e.g., MAX(LastStat65), MAX(c.LATE), and GROUP BY 1,2.

Enthusiast

Re: Many rows to one row

Thanks GJColeman,

 

that works fine. Also good to learn something new :-)

 

Regards