0

I am trying to generate the columns dynamically based upon the rows exist in the table.

I have a table like below - 

 Name  |  Sub  |  Marks
 A     |Hindi  |59
 A     |Eng    |88
 A     |Maths  |68

However, I need the result like below - 

 Name  |  Sub  |  Marks  |  Sub  |  Marks  |  Sub  |  Marks
 A     |Hindi  |59       |Eng    |88       |Maths  |68

I am clueless how to achieve the same. Any help is greatly appreciated.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • Would there always be three columns/subjects, or could this number be variable? – Tim Biegeleisen May 20 '18 at 09:28
  • This is the biggest challenge for me. These rows could be dynamic. Sometimes 2 or sometimes 10. – Ankit Bajpai May 20 '18 at 09:29
  • It is interesting as to how many questions are being asked for dynamic `pivot`, but unfortunately there isn't a straightforward way yet other than xml or constructing columns and running dynamic sql. https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql – Kaushik Nayak May 20 '18 at 09:33
  • @KaushikNayak, This can never be my solution as i don't want the values to become columns, Rather add more columns with the same name. – Ankit Bajpai May 20 '18 at 09:35

1 Answers1

0

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Name, Sub, Marks ) AS
SELECT 'A', 'Hindi', 59 FROM DUAL UNION ALL
SELECT 'A', 'Eng',   88 FROM DUAL UNION ALL
SELECT 'A', 'Maths', 68 FROM DUAL;

Query 1:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY name ORDER BY ROWNUM ) AS rn
  FROM   table_name t
)
PIVOT (
  MAX( Sub ) AS SUB,
  MAX( Marks ) AS Marks
  FOR rn IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 )
)

Results:

| NAME | 1_SUB | 1_MARKS | 2_SUB | 2_MARKS | 3_SUB | 3_MARKS |  4_SUB | 4_MARKS |  5_SUB | 5_MARKS |  6_SUB | 6_MARKS |  7_SUB | 7_MARKS |  8_SUB | 8_MARKS |  9_SUB | 9_MARKS | 10_SUB | 10_MARKS |
|------|-------|---------|-------|---------|-------|---------|--------|---------|--------|---------|--------|---------|--------|---------|--------|---------|--------|---------|--------|----------|
|    A | Hindi |      59 |   Eng |      88 | Maths |      68 | (null) |  (null) | (null) |  (null) | (null) |  (null) | (null) |  (null) | (null) |  (null) | (null) |  (null) | (null) |   (null) |
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Can this query be more modified to include only columns till value is non-null? Such as in this case only till 3_sub, 3_marks – Ankit Bajpai May 20 '18 at 10:56
  • @AnkitBajpai No, you need to have a known (fixed) number of columns in the query. If you want a dynamic number of columns then you will need to use dynamic SQL. You would probably be better to return the values in rows (not columns) and then transform it to columns in whatever front-end you are using to display the results. – MT0 May 20 '18 at 11:59