Oracle converts a table from row to column

Question

I have a request:

select vrec, valnum, valte from val_tb where recd in (select recd from rectb where setd = 17) AND (vid = 3 OR vid = 26 OR vid = 28); 

For the above results, I get:

 vrec valnum valte 98945823 NULL Total 98945823 NULL 06001 98945823 16.57 NULL 98945824 NULL Total 98945824 NULL 06005 98945824 0.36 NULL 

I want to convert it to get:

 98945823 06001 Total 16.57 98945824 06005 Total 0.36 

i.e. combine vrec results.

Can this be done using Oracle SQL?

Answer 1

One way to distinguish between valte values ​​is to check if a string contains only digits or not (a bad solution, but should work):

 WITH cte( vrec,valnum, valte) AS ( SELECT 98945823 AS vrec, NULL AS valnum,'Total' AS valte FROM dual UNION ALL SELECT 98945823, NULL, '06001' FROM dual UNION ALL SELECT 98945823, 16.57, NULL FROM dual UNION ALL SELECT 98945824, NULL, 'Total' FROM dual UNION ALL SELECT 98945824, NULL, '06005' FROM dual UNION ALL SELECT 98945824, 0.36, NULL FROM dual ) SELECT vrec ,MAX(CASE WHEN REGEXP_LIKE(valte, '^[[:digit:]]*$') THEN valte ELSE NULL END) ,MAX(CASE WHEN NOT REGEXP_LIKE(valte, '^[[:digit:]]*$') THEN valte ELSE NULL END) ,MAX(valnum) FROM cte GROUP BY vrec; 

SqlFiddleDemo

Output:

 ╔═══════════╦═══════════════╦═══════════════╦═════════════╗ ║ VREC ║ MAX(CASE...) ║ MAX(CASE...) ║ MAX(VALNUM) ║ ╠═══════════╬═══════════════╬═══════════════╬═════════════╣ ║ 98945823 ║ 06001 ║ Total ║ 16.57 ║ ║ 98945824 ║ 06005 ║ Total ║ 0.36 ║ ╚═══════════╩═══════════════╩═══════════════╩═════════════╝ 

For your case, exchange cte with hardcoded values ​​using:

 select vrec, valnum, valte from val_tb where recd in (select recd from rectb where setd = 17) AND (vid = 3 OR vid = 26 OR vid = 28); 

Your data structure is very bad, so this solution just gets around. You must really change the basic structure.

Answer 2

You are right that this is the simplest solution ... but you missed the group:

  select vrec, MAX(valnum),'Total' ,MAX(valte) from val_tb where recd in (select recd from rectb where setd = 17) AND (vid = 3 OR vid = 26 OR vid = 28) AND valte <>'Total' --<< Lines with constant 'Total' are of no use... GROUP BY vrec; 

Answer 3

Another option. Got the test data from @ lad2025

 WITH cte( vrec,valnum, valte) AS ( SELECT 98945823 AS vrec, NULL AS valnum,'Total' AS valte FROM dual UNION ALL SELECT 98945823, NULL, '06001' FROM dual UNION ALL SELECT 98945823, 16.57, NULL FROM dual UNION ALL SELECT 98945824, NULL, 'Total' FROM dual UNION ALL SELECT 98945824, NULL, '06005' FROM dual union all select 98945824, 0.36, null from dual ) select vrec, max(id), max(tot), sum(sum) from ( select vrec, valte id ,null tot ,null sum from cte where not valte = 'Total' union all select vrec, null ,valte ,null from cte where valte = 'Total' union all select vrec, null ,null ,to_char(valnum) from cte where valnum is not null ) group by vrec ; 

Answer 4

 select vrec, max(valte), 'Total' || max(valnum) from val_tb where recd in (select recd from rectb where setd = 17) and (vid = 3 OR vid = 26 OR vid = 28) and NVL(valte, '#') != 'Total' group by vrec; 

The idea that:

• We do not care about recording with valte from "Total". We can just add “Total” to the valte we need. Thus, we exclude records with valte from "total", preserving NULL values, therefore, we save the record with valnum .
• Only one valnum and one valte , which are NOT NULL per vrec , so we take MAX and GROUP BY vrec .

Answer 5

You can get it with a PIVOT request like:

 WITH pivot_data AS ( select vrec, valnum, valte from val_tb where recd in (select recd from rectb where setd = 17) AND (vid = 3 OR vid = 26 OR vid = 28) ) SELECT * FROM pivot_data PIVOT ( max(valte ) --<-- pivot_clause FOR table --<-- pivot_for_clause IN (FORM Hidden FIELD Name) --<-- pivot_in_clause ); 

For the Dynamic IN clause, create a hidden form and pass the following query result to it. Then go into this field to the IN clause of this query.

 SELECT LISTAGG(dbms_assert.enquote_literal(valnum ), ', ') WITHIN GROUP (ORDER BY valnum ) valnum FROM (select valnum from val_tb where recd in (select recd from rectb where setd = 17) AND (vid = 3 OR vid = 26 OR vid = 28) and valnum is not null)