CODEWITHSUNDEEP
sqloracle-database
Mohd Shaffiq Exatech
Mohd Shaffiq Exatech

Reputation: 11

sql to output multiple data in single row

I have these set of data in oracle :

app_name  batch_group  session_total 
--------  ------------ -------------
app1      b1              5
app2      b1              10
app1      b2              80
app2      b2              99
app1      b3              20
app2      b3              59

I want to come out data with these format in oracle

app_name  session1  session2 session3
--------  --------  -------- --------
app1      5          80       20
app2      10         99       59

session will increase base on how many batch. if got 6 batch in will come out until session6. please help me because im new in sql

Upvotes: 1

Views: 60

Answers (1)

MT0
MT0

Reputation: 168681

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TEST ( app_name,  batch_group,  session_total ) AS
          SELECT 'app1', 'b1',  5 FROM DUAL
UNION ALL SELECT 'app2', 'b1', 10 FROM DUAL
UNION ALL SELECT 'app1', 'b2', 80 FROM DUAL
UNION ALL SELECT 'app2', 'b2', 99 FROM DUAL
UNION ALL SELECT 'app1', 'b3', 20 FROM DUAL
UNION ALL SELECT 'app2', 'b3', 59 FROM DUAL;

Query 1:

SELECT app_name,
       SUM( CASE batch_group WHEN 'b1' THEN session_total END ) AS session1,
       SUM( CASE batch_group WHEN 'b2' THEN session_total END ) AS session2,
       SUM( CASE batch_group WHEN 'b3' THEN session_total END ) AS session3
FROM   TEST
GROUP BY app_name

Results:

| APP_NAME | SESSION1 | SESSION2 | SESSION3 |
|----------|----------|----------|----------|
|     app1 |        5 |       80 |       20 |
|     app2 |       10 |       99 |       59 |

Query 2:

SELECT * FROM
(
  SELECT *
  FROM   TEST
)
PIVOT 
(
  SUM( session_total)
  FOR batch_group
  IN (
    'b1' AS session1,
    'b2' AS session2,
    'b3' AS session3
  )
)

Results:

| APP_NAME | SESSION1 | SESSION2 | SESSION3 |
|----------|----------|----------|----------|
|     app1 |        5 |       80 |       20 |
|     app2 |       10 |       99 |       59 |

Upvotes: 1

Related Questions