Can anyone help me to solve below sql question?-CodePudding

INPUT :-TABLENAME :- T1

ALGO|QTR|VOL
A   Q1  1
A   Q2  2
A   Q3  3
B   Q1  4
B   Q2  5
B   Q3  6
C   Q1  7
C   Q2  8
C   Q3  9

enter image description here

OUTPUT:

ALGO  Q1    Q2  Q3
A      1    2   3
B      4    5   6
C      7    8   9

enter image description here I tried pivot in sql but giving syntax error. Can any one help me for the same ?

CodePudding user response：

You can do a manual/static pivot, as in:

select
  algo,
  max(case when qtr = 'Q1' then vol end) as q1,
  max(case when qtr = 'Q2' then vol end) as q2,
  max(case when qtr = 'Q3' then vol end) as q3
from t1
group by algo

If you need the pivot to be dynamic, then you can use two SQL selects: the first one can retrieve the possible column names; with it you can assemble a second query (using the same form as shown in this answer) that will produce columns dynamically.

CodePudding user response：

I like The Imapaler's solution and it is likely better than mine.

Below an alternative that might be a lot more expensive, but also shows a different approach. It is not guaranteed that it will show a worse performance though. Database systems often manage to surprise me.

select a.algo, a.vol as Q1, b.vol as Q2, c.vol as Q3
  from T a, T b, T c
 where a.algo = b.algo
   and b.algo = c.algo
   and a.qtr = 'Q1'
   and b.qtr = 'Q2'
   and c.qtr = 'Q3';

Note: some people are absolutely in love with the join operator. It'll be easy to convert the above to use the join operator. The logic remains the same.