0

Problem: I have to make each row as column using SQL statement.

Source Table

Colour_id   Colour_desc Value1_min  Value1_max  Value2_min  Value2_max
1           Green          0          0.499        0        0.299
2           Amber          0.5        0.8         0.03      0.05

I want a output looks like below:

Value1_min_Green    Value1_max_Green    Value2_min_Green    Value2_max_Green    Value1_min_Amber    Value1_max_Green    Value2_min_Amber    Value2_max_Green
0                     0.499               0                 0.299          0.5                       0.8                0.03                           0.05

Any Approach or suggestion to get above result as output?

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
Ganesh
  • 486
  • 3
  • 8
  • 18
  • 2
    Duplicate of [Pivoting of data using two columns](https://stackoverflow.com/questions/11987067/pivoting-of-data-using-two-columns) – Kaushik Nayak Jul 02 '18 at 10:21

2 Answers2

1

Combine the 2 rows value in 1 row output (source is table name):

select green.Value1_min as Value1_min_Green green.Value1_max as Value1_max_Green,
       green.Value2_min as Value2_min_Green green.Value2_max as Value2_max_Green,
       amber.Value1_min as Value1_min_Amber amber.Value1_max as Value1_max_Amber,
       amber.Value2_min as Value2_min_Amber amber.Value2_max as Value2_max_Amber
  from source as green, source as amber
 where amber.Colour_desc = 'Amber'
   and green.Colour_desc = 'Green'
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
1

You can use conditional aggregation:

select max(case when colour_desc = 'green' then value1_min end) as value1_min_green,
       max(case when colour_desc = 'green' then value2_min end) as value2_min_green,
       max(case when colour_desc = 'amber' then value1_min end) as value1_min_amber,
       max(case when colour_desc = 'amber' then value2_min end) as value2_min_amber
from t;

Adding additional colors is quite easy -- and really incurs no additional performance overhead because the query is already doing an aggregation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon, but it's not working for my scenario. since i want to restrict the output data to only 1 rows. but while running your approach, i am getting all the 3 rows. – Ganesh Jul 02 '18 at 11:05
  • @Ganesh : You should use `MAX (` for all the case expressions – Kaushik Nayak Jul 02 '18 at 11:38