asked
How to interchange row and column in sql 2005
I have created a database to make a class timetable. Table fields are, PeriodNo int Class_ID int Div_ID int Date datetime Subject_ID int StaffNo int LabOrRoom varchar(50) MaxPeriodperDay int And I want to display it as follows, 1 2 3 4 5 Mon math Sci comp Hist Eng Tue Sci math Hist Hist math Wed math Sci Eng comp Eng Thu Eng Hist comp Hist math Fri Hist Sci math comp Eng Please help me to write the perfect query for this. Thank you, Deepti
I guess you should provide more information about the other tables and their sample data as well. Moreover, the output seems to be only for one class, what about the other classes? Or you need just one class's data?
Just for a one class data for particular week. And what u want to know about other tables ? because fields like class ,division ,subject and staff are taken from other tables.
There is a command in sql server named pivot to interchange rows into columns or columns into rows pivot: interchanges column to rows unpivot: row to column (SELECT distinct xyz,[Standard],[Next],[Best] into #tmtbl1 FROM ( SELECT D,i,F,score,[in],type,PROCESS_ID FROM T_Result GROUP BY type,D,F,i,score,[in],PROCESS_ID )PS PIVOT ( SUM(process_id) FOR TYPE IN ([Standard],[Next],[Best]) )AS PVT unpivot ( typex for xyz in(d,i,score,[in],f) ) AS xyz1 group by xyz,[Standard],[Next],[Best] ) select xyz,sum(Standard) from #tmtbl1 group by xyz
how to interchange two column data with column names
You might want to ask this as a new question rather than revive a question asked five years ago. And provide more detail about what it is you're trying to do.
7 People are following this question.
