How to change the columns of SQL Query output or table to rows?

If you are database programmer, you may have come across this issue. There may be cases in which user should be allowed to enter data column wise.

 Here is an example. Suppose, for a manufacturing company, a user should enter plan volume to be produced in month wise basis. The screen provided should be like this.

 

Head	Year	Shrawan	Bhadra	Aswin	Kartik	Mangsir	Poush	Magh	Falgun	Chiatra	Baiskh	Jestha	Asadh
Plan Volume	2065	2.3	33.2	12	2	4	5	2.1	3.2	2.0	1.2	5.6	2.1

 

In above figure, Shrawan to Asadh are Nepali Months. The application should provide the user to enter Plan Volume data from screen as shown above. So, the main table structure should be same as above. But for calculation purpose and manipulation, it is often required to retrieve same data as shown below.

 

Head	Year	Month	Amt
Plan Volume	2065	Shrawan	2.3
Plan Volume	2065	Bhadra	33.2
Plan Volume	2065	Aswin	12
Plan Volume	2065	Kartik	2
Plan Volume	2065	Mangsir	4
Plan Volume	2065	Poush	5
Plan Volume	2065	Magh	2.1
Plan Volume	2065	Falgun	3.2
Plan Volume	2065	Chaitra	2.0
Plan Volume	2065	Baisakh	1.2
Plan Volume	2065	Jestha	5.6
Plan Volume	2065	Asadh	2.1

 

The data are same as above.

This can be achieved by a simple trick of SQL. The second data structure can be achieved by creating a view. This is logical as the no. of columns is known previously.

Suppose, if the previous data table name is "Tbl_plan". The created view will be like this.

 

create view Tbl_plan_vw

as

 select    [head], [year], 'Shrawan' [Month], [Shrawan]

from     Tbl_plan

union all

select    [head], [year], 'Bhadra' [Month], [Bhadra]

from     Tbl_plan

union all

select    [head], [year], 'Aswin' [Month], [Aswin]

from     Tbl_plan

union all

select    [head], [year], 'Kartik' [Month], [Bhadra]

from     Tbl_plan

union all

select    [head], [year], 'Mangsir' [Month], [Mangsir]

from     Tbl_plan

union all

select    [head], [year], 'Poush' [Month], [Poush]

from     Tbl_plan

union all

select    [head], [year], 'Magh' [Month], [Magh]

from     Tbl_plan

union all

select    [head], [year], 'Falgun' [Month], [Magh]

from     Tbl_plan

union all

select    [head], [year], 'Chaitra' [Month], [Chaitra]

from     Tbl_plan

union all

select    [head], [year], 'Baisakh' [Month], [Baisakh]

from     Tbl_plan

union all

select    [head], [year], 'Jestha' [Month], [Jestha]

from     Tbl_plan

union all

select    [head], [year], 'Asadh' [Month], [Asadh]

from     Tbl_plan

 

 

The above technique is more appropriate when the no. of columns is less.

If the data in Tbl_plan is to be shown directly in report (Excel Report, Crystal Report) then, pivoting can be directly done in the report from Tbl_plan, without needing to create additional view.