SQLTeam.com | Weblogs | Forums

Convert Rows into Columns


#1

Dear All,

I am getting following output. Now I want to convert Rows to Columns. I have mentioned in Desired Output.
Please help me how to convert rows to columns.

Output:

EmpNo EmpName HoursFromDate Time Range Short_Extra_Type Status NoOfHours
123 ABC 27 Nov 201 12:00 AM to 02:00 AM Short P 2
123 ABC 28 Nov 201 12:00 AM to 03:30 AM Extra A 5

Desired Output:

EmpNo EmpName ShortHoursFromDate ExtraHoursFromDate ShortTimeRange ExtraTimeRange
123 ABC 27 Nov 201 28 Nov 201 12:00 AM to 02:00 AM 12:00 AM to 03:30 AM

Short_Type Extra_Type ShortStatus ExtraStatus ShortNoOfHours ExtraNoOfHours
Short Extra P A 2 5

Thanks,
Harish Patil


#2

There are several ways that you can transform data from multiple rows into columns. In SQL Server you can use the PIVOT function to transform the data from rows to columns:

Example: > > select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

To know more follow this article. http://www.databasejournal.com/features/mssql/converting-rows-to-columns-pivot-and-columns-to-rows-unpivot-in-sql-server.html