SQL Tutorial : Converting Rows to Columns

In this tutorial we can see how to convert rows into columns using Structured Query language SQL. We cover this tutorial from the basic steps i.e creating tables and inserting values into the table so that it can be useful for basic users as well

Start with creating a table to explain through example

CREATE TABLE EMPLOYEE_TEST (YEAR_OF_JOINING Number,
QUARTER Number, RATING Number(4,3))
Insert into EMPLOYEE_TEST
    (YEAR_OF_JOINING, QUARTER, RATING)
Values
    (2008, 1, 3.534);
COMMIT;

Table contains the following data

What we want is a query that shows one row per year with each column being the results of the Rating by quarter (i.e. one column per quarter). This is how it will appear.

Here is the query that gives this to you

SELECT YEAR_OF_JOINING,
     MAX(CASE WHEN Quarter = 1
          THEN RATING END) AS Q1,
     MAX(CASE WHEN Quarter = 2
          THEN RATING END) AS Q2,
     MAX(CASE WHEN Quarter = 3
          THEN RATING END) AS Q3,
     MAX(CASE WHEN Quarter = 4
          THEN RATING END) AS Q4
FROM Employee_test
GROUP BY YEAR_OF_JOINING;

Through this query we scan the EMPLOYEE_TEST table and return the YEAR_OF_JOINING followed by 4 quarterly ratings.

Here, we can also see the result in many other ways just by adding and removing the extra conditions. If we ignore the GROUP BY for one moment, the value of the 4 columns will be either the value in the “Rating” column if the Quarter is equal to the Quarter in the CASE expression or it will be NULL if it doesn’t match.

Here is what the results would look like if there was no GROUP BY clause (and no MAX function).

Note: The advantage of using MAX function is that it can be used with String data type also.

Generation of Data in Report Format

Let’s take example first, then through example I will explain how it is giving out the result. ROLLUP: This group by operation is used to produce subtotal at any level of aggregation needed. These subtotals then roll up into a grand total, according to items listed in the "GROUP BY" expression. The totaling is based on a one-dimensional data hierarchy of grouped information. So if we look at the query below, we have got the sum of ratings for both year 2008, 2009 and then a grand total of both the year.

select s.YEAR_OF_JOINING, s.QUARTER, sum(s.rating) SUM_OF_RATINGS from
EMPLOYEE_TEST s
group by rollup (s.year_of_joining, s.quarter)

The output is :

CUBE: This group by operation is also used to produce subtotal at any level of aggregation needed. So if we look at the query below, we have got the sum of ratings for both year 2008, 2009 along with that we will also get the aggregate for each quarter and then a grand total of both the year

select s.YEAR_OF_JOINING, s.QUARTER, sum(s.rating) AS SUM_OF_RATINGS from
EMPLOYEE_TEST s
group by cube (s.year_of_joining, s.quarter)
ORDER BY YEAROFJOINING,QUARTER

The output is:

Note: NULL values in the output of ROLLUP operations typically mean that the row contains subtotal or grand total information. We can also use NVL() function to substitute to a more meaningful value.

Related Posts

1. SQL Tutorial : Using RANK() and DENSE_RANK()
2. SQL TIPS : Writing Multiple Column Subqueries
3. T-SQL Script to Divide One Column Data into Two Columns
4. UNIX Command “find” – Tutorial
5. SQL Server 2008 New DATETIME Data Types

Tags: Converting Rows to Columns., CUBE, GROUP BY, oracle tutorial, ROLLUP, SQL, SQL TIPS, SQL Tutorial