Convert Rows Into Column

Question

Post reply

Convert Rows Into Column

ankur11june

Grasshopper

Points: 19
More actions
August 30, 2011 at 3:14 am

#243912

Hi All,
i have a requirement in which i have to convert my rows into column with some specific condition , lets have a look on below example.
Original Table :
IdProjectidMonHourTueHourWedHourThHourFriHourSatHour
1 xxx 8 6 4 6 5 3
2 yyy 7 6 5 8 9 9
Resultant Table Should be look like.
Id ProjectId Day Hours
1 xxx Mon 8
1 xxx Tue 6
1 xxx Wed 4
1 xxx Th 6
1 xxx Fri 5
1 xxx Sat 3
2 yyy Mon 7
...................................
Although i m doing this thing with the help of cursor but it is taking a lot of time which i can not live with.
Please suggest some solution of doing that.
@Please have a look on the attached File if you are not getting the example given here.
Thanks,
Ankur

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 1

Cursor is not always the best way to solve a problem. Read up on cons of using a cursor.

Do post some data scripts the next time. And it is columns to rows not the other way.

if object_id('ProjectHours') is not null drop table ProjectHours;

go

create table ProjectHours(ID int, ProjectID char(3), Mon int, Tue int, Wed int, Thur int, Fri int);

insert into ProjectHours(ID, ProjectID, Mon, Tue, Wed, Thur, Fri)

values

(1,'xxx',8,6,4,6,5),

(2,'yyy',7,6,5,8,9)

select * from ProjectHours

--UNPIVOT

select ID, ProjectID, [Day], Hrs

from

(select ID, ProjectID, Mon, Tue, Wed, Thur, Fri from ProjectHours) p

UNPIVOT

(

Hrs for [Day] IN (Mon, Tue, Wed, Thur, Fri)

)as unpvt;

https://sqlroadie.com/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

;WITH SourceTable AS (

SELECT Id = 1, Projectid = 'xxx', MonHour = 8, TueHour = 6, WedHour = 4, ThHour = 6, FriHour = 5, SatHour = 3 UNION ALL

SELECT 2, 'yyy', 7, 6, 5, 8, 9, 9)

SELECT Id, Projectid, [Day] = 'mon', [Hours] = MonHour FROM SourceTable UNION ALL

SELECT Id, Projectid, [Day] = 'Tue', [Hours] = TueHour FROM SourceTable UNION ALL

SELECT Id, Projectid, [Day] = 'Wed', [Hours] = WedHour FROM SourceTable UNION ALL

SELECT Id, Projectid, [Day] = 'th', [Hours] = ThHour FROM SourceTable UNION ALL

SELECT Id, Projectid, [Day] = 'Fri', [Hours] = FriHour FROM SourceTable UNION ALL

SELECT Id, Projectid, [Day] = 'Sat', [Hours] = SatHour FROM SourceTable

ORDER BY Id, Projectid

Don't forget sunday 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 3

I don't work on Saturday and Sunday 😉

https://sqlroadie.com/