Vijay Vijay - 2 months ago 5
SQL Question

SQL query to transpose some columns

I have a table like this 3 columns:
col1,col2,col3

I have to group these three column and calculate the count.
I have completed the group by like below:

select col5,col3,col2, count(*) from mytable group by col5,col3,col2

COL5 COL3 COL2 COUNT(*)
MOVIL A PRE 81.00
MOVIL B COM 466.00
MOVIL A COM 947.00


There are only two possible value's in col2 which are PRE and COM
But What I need is a small change like below:

COL5 COL3 PRE COM
MOVIL A 81 947
MOVIL B NULL 466


I believe PIVOT should be used for this.
But I am not an expert in SQL. Could anybody please help?

Answer

You can use PIVOT like this (I think col2 is having only two values and not col3):

SELECT *
FROM (select col5,col3,col2, count(*) from mytable group by col5,col3,col2)
PIVOT(MAX(CNT) FOR COL2 IN ('PRE','COM'));
Comments