Oracle SQL - Concat rows with specific properties

Question

I wrote a SQL - Statement to export the results to an excel sheet for analytics. At the moment I have the problem:

Some data which are split in 2 rows but need it as 1 row. Reason for that is one column, with different values.

I explain the structure: The sql create this table (headers):

ROOT|INSERT_TS|COUNT_ALL|Order_Type|Input_Obj|Input_SUPP|IS_SIM|Last_TS|Status_Type|Count_Open

Example data with my problem (this is the result what give my actual SQL - Statement):

R |In_TS|C_AL|O|I_Obj|I_Supp|IS_Sim|La_TS|Status|C_Opn|
--+-----+----+-+-----+------+------+-----+------+-----+
76|date1|1451|a|file1|mass  |1     |date2|work  |1451 | <-- 1st part
76|date1|25  |a|file1|mass  |1     |date2|final |0    | <-- 2nd part
76|date1|1   |b|file1|man   |0     |date2|final |0    |
76|date1|1   |c|file1|mass  |1     |date2|work  |1    |

What I want at the end is this (My target after fixing the sql query):

R |In_TS|C_AL|O|I_Obj|I_Supp|IS_Sim|La_TS|Status|C_Opn|
--+-----+----+-+-----+------+------+-----+------+-----+
76|date1|1476|a|file1|mass  |1     |date2|work  |1451 | <-- 1 row
76|date1|1   |b|file1|man   |0     |date2|final |0    |
76|date1|1   |c|file1|mass  |1     |date2|work  |1    |

Thats my actual sql query/statement:

SELECT 
  distinct t1.ROOT r, 
  min(to_char(t1.INSERT_TS, 'YYYY.MM.DD HH24:MI')) in_ts,
  count(distinct t1.tool_ID) c_al, -- this count give the number of modul steps in this "business tool" - no sum task is here needed
  t1.ORDERTYPE o,
  t1.I_OBJ,
  t1.I_Supp, 
  t1.IS_SIM, 
  max(to_char(t1.LASTCHANGE_TS, 'DD.MM.YYYY HH24:MI')) la_ts,
  t2.STATUS status,
  sum(case when t2.STATUS != 'final' then 1 else 0 end) c_opn
FROM TOOL_DATA.FW_MAIN t1
left join TOOL_DATA.CONF_STATUS t2 on t2.tab = 'FW_MAIN' and t2.status_val = t1.status
GROUP BY 
  t1.ROOT, t1.ORDERTYPE, t1.I_OBJ, t1.I_SUPP, t1.I_SIM, t2.STATUS
ORDER BY 2 desc, 1 desc
;

What I do and search to solve this problem on my own:

I look here in this community and in google, found some task about own define functions and listagg, but both doesn't help, because the col "Status" grew up, but the row does not combine.

Have someone here any idea(s) for me to solve this?

The important thing is:

Col "O" is identical
Col "R" is identical
Col "IS_SIM" is identical
Col "C_AL" is cumulative
Col "C_Opn" sum only the not "final" data sets (better: both values are cumulativ, cuz xx + 0 = xx)
Col "Status" show only the "work" value, if the rows are combined, the "final" value is removed

The solution (thx to ruudvan)

SELECT 
  distinct t1.ROOT r, 
  min(to_char(t1.INSERT_TS, 'YYYY.MM.DD HH24:MI')) in_ts,
  count(distinct t1.tool_ID) c_al, -- this count give the number of modul steps in this "business tool" - no sum task is here needed
  t1.ORDERTYPE o,
  t1.I_OBJ,
  t1.I_Supp, 
  t1.IS_SIM, 
  max(to_char(t1.LASTCHANGE_TS, 'DD.MM.YYYY HH24:MI')) la_ts,
  NVL(MAX(case when t2.STATUS != 'final' then t2.STATUS else null end), 'final'), -- i set it to != 'final', because status have more as 1 work value, but i wrote it here for a simple view
  sum(case when t2.STATUS != 'final' then 1 else 0 end) c_opn
FROM TOOL_DATA.FW_MAIN t1
left join TOOL_DATA.CONF_STATUS t2 on t2.tab = 'FW_MAIN' and t2.status_val = t1.status
GROUP BY 
  t1.ROOT, t1.ORDERTYPE, t1.I_OBJ, t1.I_SUPP, t1.I_SIM,
ORDER BY 2 desc, 1 desc
;

Since all GROUP BY columns are also in the select-list, you can remove DISTINCT. — jarlh
– jarlh, Commented Feb 26, 2015 at 14:47
Normally perhabs @jarlh , but not here with this db logging over business processing. If I did it, i get more rows which i need for analytics. I test it and its important ;) But thx. — Lyçann H.
– Lyçann H., Commented Feb 26, 2015 at 14:50
The question is confusing because the problematic data set you showed us is actually the result of the query in the question. You would get more accurate answers if you showed us the actual data set on which you are running the query and getting the result that you want to fix further. See my answer regardless. — ruudvan
– ruudvan, Commented Feb 26, 2015 at 15:18
Thanks for this information and tipp ruudvan. I will include this in my next questions. — Lyçann H.
– Lyçann H., Commented Feb 26, 2015 at 15:24
I edit this question here with some more informations, hope it is more understandable now — Lyçann H.
– Lyçann H., Commented Feb 26, 2015 at 15:26

ruudvan · Accepted Answer · 2015-02-26 15:13:59Z

2

Start with this -

Group by should be on the identical columns and then calculate the other values.

So remove t2.STATUS from GROUP BY clause. Instead replace the

SELECT ... t2.STATUS

to

SELECT ... NVL(MAX(case when t2.STATUS = 'work' then t2.STATUS else null end), 'final')

Here case when t2.STATUS = 'work' then t2.STATUS else null end would ensure that all values in the same group that have the status as 'final' would be null and 'work' values would stay as-is. Doing a MAX (or MIN) on them would pick the non-null value which will be 'work'.

If however, there is no 'work' value: NVL ensures that if all values are 'final' then the MAX() would result in null, and in that case, return 'final' as the result.

SELECT ... COUNT(distinct t1.tool_id) c_al would remain the same as it will now count tool_ids for the whole group.

edited Feb 26, 2015 at 15:13

answered Feb 26, 2015 at 14:54

ruudvan

1,3816 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lyçann H. Over a year ago

Yes, that was the solution. Thank you very much @ruudvan !!

Ankit Bajpai · Accepted Answer · 2015-02-26 14:50:04Z

0

Try this:-

SELECT 
distinct t1.ROOT r, 
min(to_char(t1.INSERT_TS, 'YYYY.MM.DD HH24:MI')) in_ts,
sum(distinct t1.tool_ID) c_al,
t1.ORDERTYPE o,
t1.I_OBJ,
t1.I_Supp, 
t1.IS_SIM, 
max(to_char(t1.LASTCHANGE_TS, 'DD.MM.YYYY HH24:MI')) la_ts,
t2.STATUS status,
sum(case when t2.STATUS != 'final' then 1 else 0 end) c_opn
FROM TOOL_DATA.FW_MAIN t1
left join TOOL_DATA.CONF_STATUS t2 on t2.tab = 'FW_MAIN' and t2.status_val = t1.status
GROUP BY t1.ROOT, t1.ORDERTYPE, t1.I_OBJ, t1.I_SUPP, t1.I_SIM, t2.STATUS
ORDER BY 2 desc, 1 desc;

answered Feb 26, 2015 at 14:50

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

1 Comment

Lyçann H. Over a year ago

It doesn't help, cuz the t1.tool_id is a bigger number, but i need not the sum of these id numbers, i need the summ of number of tool_id (At Info: the number of tool_id give the information about how many modul steps it gives in this "tool" to processing the business order)

Collectives™ on Stack Overflow

Oracle SQL - Concat rows with specific properties

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related