Rows to Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rows to Columns

Author

Topic

velvet_tiger
Starting Member

12 Posts

Posted - 2013-09-18 : 13:30:11

Hi Guys,

I have the following results from a query that I am running.


Days	0810-0900	0910-1000	1010-1100	1110-1200	1210-1300	1210-1300	1310-1400	1410-1500	1510-1600	1610-1700	1710-1800	1810-1900	2010-2100
2Monday	0810-0900	0910-1000	1010-1100	1110-1200	1210-1300	10898	10898	10898	11494	11494	11497	11497	2010-2100

Basically, the columns called 0910-1000 etc represents time slots on a timetable. As you can see the 0910-1000 field has a corresponding 0910-1000 in that column. That means that the 0910-1000 slot has no classes scheduled at that time on that day. The other slots that have a five digit number means that a class is schedule at that particular time spot.

I would like to convert this list into the following format


Day	Time
2Monday	0810-0900
2Monday	0910-1000
2Monday	1010-1100
2Monday	1110-1200
2Monday	1210-1300
2Monday	10898
2Monday	10898
2Monday	10898
2Monday	11494
2Monday	11494
2Monday	11497
2Monday	11497
2Monday	2010-2100

I know that the initial format is ideal but I would like to place this code in ireports so that users can select a day and a time.

Any ideas

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-18 : 14:24:13

Unless you want to show us the origianl query, I think you'd have to use a Dynamic Pivot in order to do someting like that.

http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server

EDIT: Forgot link to dynamic pivot sample

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-18 : 22:35:01

I think you need unpivot as its converting to rows that you want. Your heading said opposite though


SELECT *
FROM
(
Your current query
)q
UNPIVOT (Cat FOR Time IN ([0810-0900],[0910-1000],[1010-1100],...))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-19 : 08:14:34

http://blog.jontav.com/post/8344518585/convert-rows-to-columns-columns-to-rows-in-sql-server

veeranjaneyulu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources