I am using oracle sql.

below you will find a query and its result. i uploaded as much i could, so you can get an idea.

**Query**

Select DISTINCT GREATEST(0, ...) as col1, GREATEST(0, ...) as col2, SUM(b1) as col3, SUM(b2) as col4, SUM(CASE WHEN ...) as col5 From mainTable m left join ( Select DISTINCT MAX(...) as a1 from mainTable m left join ... where ... )... left join ( Select DISTINCT MAX(Case WHEN ...) as b1, MAX(Case WHEN ...) as b2, MAX(Case WHEN ...) as b3, MAX(Case WHEN ...) as b4 from mainTable m left join ... left join ... left join ... where ... )... left join ... left join ... WHERE ...

**Query Results**

|------|------|------|-----| | col1 | col2 | col3 | ... | |------|------|------|-----| | 12 | 2 | 56 | ... | |------|------|------|-----|

**But I want the following results:**

|------|------| | col1 | 12 | |------|------| | col2 | 2 | |------|------| | col3 | 56 | |------|------| | ... | ... | |------|------|

so i took my orginal query and put it in sub select statment and added pivot tag in end.

**error:**ORA-00907: missing right parenthesis

**line:**pivot ((myname, Total_Counts) FOR Category IN (

select * from ( Select DISTINCT GREATEST(0, ...) as col1, GREATEST(0, ...) as col2, SUM(b1) as col3, SUM(b2) as col4, SUM(CASE WHEN ...) as col5 From mainTable m left join ( Select DISTINCT MAX(...) as a1 from mainTable m left join ... where ... )... left join ( Select DISTINCT MAX(Case WHEN ...) as b1, MAX(Case WHEN ...) as b2, MAX(Case WHEN ...) as b3, MAX(Case WHEN ...) as b4 from mainTable m left join ... left join ... left join ... where ... )... left join ... left join ... WHERE ... ) pivot ((myname, Total_Counts) FOR Category IN ( ( col1 AS 'col1', col2 AS 'col2', col3 AS 'col3', col4 AS 'col4', col5 AS 'col5' ) );

This post has been edited by **ikhlas06**: 08 January 2018 - 10:18 AM