How Do I Rotate Datatable Rows Into Columns and display In Gridview

Question

0.00/5 (No votes)

See more:

Hai all,

I have 3 tables called timetablemaster,timetabledetails and staff.

Here are my table Structure

timetablemaster

ttId, ttTitle, classCode, sectionCode, startTime, validFrom, validTo, startDate, endDay, noOfClasses

timetabledetails

ttDetailsId, ttId, day, period, subjectCode, staffCode

staff

staffId, staffCode, firstName

SQL

select  s.firstName as Staff, tm.classCode as Class, td.day as Day, td.period as Periods
from timetabledetails td, timetablemaster tm, staff s where s.staffCode = td.staffCode and td.ttId = tm.ttId and td.staffCode like 'S01';

From this query i am getting this format

Staff | Class | Day | Period |

Abc | STD1 | Monday | 1 |
Abc | STD1 | Tuesday| 5 |
Abc | STD1 | Wednesday | 4 |
Abc | STD1 | Thursday | 2 |
Abc | STD1 | Friday | 3 |
Abc | STD1 | Saturday | 3 |
Abc | STD2 | Monday | 5 |
Abc | STD2 | Tuesday| 4 |
Abc | STD2 | Wednesday | 3 |
Abc | STD2 | Thursday | 2 |
Abc | STD2 | Friday | 1 |
.
.
.
.

But I need to display the Table in this format in Gridview

|Monday|

StaffName | Period1 | Period2 | Period3 | Period4 | Period5

Abc | STD1 | STD2 | STD3 | STD1 | STD2
.
.
Xyz | STD3 | STD5 | STD1 | STD4 | STD1
Pqr | STD2 | STD3 | STD4 | STD1 | STD3
.
.
.
.

Anybody know how to do this? Thanks very much!

Posted 20-Mar-14 0:21am

Mohammed_Faisal_Majeed

Updated 20-Mar-14 4:09am

v15

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tom Marvolo Riddle · Answer 1 · 2014-03-20T00:28:00

Solution 1

Your query will fetch the record as itself.

Use Pivot to change from rows into columns.

Refer:
Cross-Tabulation (Pivot Tables) with MySQL[^]
http://mgenev.blogspot.in/2010/07/pivot-tables-in-mysql.html[^]
http://en.wikibooks.org/wiki/Programming:MySQL/Pivot_table[^]

Posted 20-Mar-14 0:28am

Tom Marvolo Riddle

Updated 20-Mar-14 0:29am

v2

Comments

[no name] 20-Mar-14 9:24am

Hai Jas, I am not able to do it from u r given link, can u please help me

Tom Marvolo Riddle 20-Mar-14 23:58pm

what-have-you-tried?[^]

[no name] 21-Mar-14 0:02am

I am retrieving table from Database and storing in DataTable and from Datatable i am trying to make that format... which is not happening ... is there any other way

Tom Marvolo Riddle 21-Mar-14 0:15am

Afaik PIVOT is the only way to achieve this.Just refer the link and customize it

[no name] 21-Mar-14 0:16am

I tried but i am not getting :(