Oracle transform table from row to column

Question

I have a query:

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 results above, 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 transform it to get:

98945823    06001   Total   16.57
98945824    06005   Total   0.36

i.e. combine results by vrec.

Is it possible to do this using Oracle SQL?

@dang..there have been a series of questions from you since yesterday. You should put in some effort to solve them. — Vamsi Prabhala, Dec 10 '15 at 17:15
Possible duplicate of [How can I get column names from a table in Oracle?](http://stackoverflow.com/questions/452464/how-can-i-get-column-names-from-a-table-in-oracle) — Shantanu Nandan, Dec 10 '15 at 17:17
Mostly, inefficient DB designs will make this kind of complexities. — Mohamed Saligh, Dec 10 '15 at 17:30
Unless there are some other columns you are not showing, there is no way to differentiate between the two rows with valnum of NULL. — OldProgrammer, Dec 14 '15 at 16:40
Could you please amend the result of `select vid, 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);` Probably column `vid` is used to distinguish the row entries? — Frank Ockenfuss, Dec 16 '15 at 07:56

Lukasz Szozda · Accepted Answer · 2015-12-19T06:57:52.567

One way to differentiate between valte values can be checking if string contains only digits or not (poor 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 hardcoded values with:

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 poor, so this solution is just workaround. You should really change underlying structure.

score 0 · Answer 2 · edited Dec 17 '15 at 17:21

0

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

 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;

edited Dec 17 '15 at 17:21

TARehman

6,659
3
33
60

answered Dec 16 '15 at 06:30

Jibin Balachandran

3,381
1
24
38

score 0 · Answer 3 · answered Dec 16 '15 at 13:26

Yet one more option. Grabbed 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
;

Robert Bain · Answer 4 · 2015-12-16T20:56:46.443

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 thinking behind this is that:

We don't care about the record with a valte of 'Total'. We can just prepend 'Total' to the valte we require. So we exclude records with a valte of 'total', keeping the NULL values, so we retain record with a valnum.
There's only one valnum and one valte that are NOT NULL per vrec, so we take the MAX and GROUP BY the vrec.

score 0 · Answer 5 · answered Dec 17 '15 at 09:30

You can get it using PIVOT query as:

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 Dynamic IN clause, create a form hidden filed and pass the following query result into this. Then refer that field to IN clause of the above 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)

Oracle transform table from row to column

5 Answers5