HOW CAN I CONVERT THE COLUMNS TO ROWS

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

SQL Server 2000 Forums

SQL Server Development (2000)

HOW CAN I CONVERT THE COLUMNS TO ROWS

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-07-19 : 04:57:32

DECLARE @STUDENT TABLE
(
SID INT,
SNAME VARCHAR(20),
MARK1 INT,
MARK2 INT,
MARK3 INT,
MARK4 INT,
MARK5 INT
)

INSERT INTO @STUDENT
select 1,'SONE',80,70,50,60,70 UNION all
select 2,'STWO',60,65,85,76,80 UNION all
select 3,'Sthree',60,65,85,76,80

my Table Structure is like the above. but i want to
select in the rowwise,
means,
Sone stwo Sthree
Mark1
Mark2
Mark3
Mark4
Mark5
Can we get this in the query it self, or do i need to do it in the front end. Advise pls

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-19 : 05:55:56

It is not clear what you want. Try one of these

-- This vector?
select		z.value
from		(
			select	10 * sid grp, sname value from @student
			union all
			select 10 * sid +1, convert(varchar, mark1) from @student
			union all
			select 10 * sid +2, convert(varchar, mark2) from @student
			union all
			select 10 * sid +3, convert(varchar, mark3) from @student
			union all
			select 10 * sid +4, convert(varchar, mark4) from @student
			union all
			select 10 * sid +5, convert(varchar, mark5) from @student
		) z
order by	z.grp

-- Or this matrix?
SELECT		MAX(CASE WHEN sid = 1 THEN mark ELSE 0 END) 'Sone',
		MAX(CASE WHEN sid = 2 THEN mark ELSE 0 END) 'Stwo',
		MAX(CASE WHEN sid = 3 THEN mark ELSE 0 END) 'Sthree'
FROM		(
			SELECT sid, 1 Lvl, mark1 mark FROM @Student UNION ALL
			SELECT sid, 2, mark2 FROM @Student UNION ALL
			SELECT sid, 3, mark3 FROM @Student UNION ALL
			SELECT sid, 4, mark4 FROM @Student UNION ALL
			SELECT sid, 5, mark5 FROM @Student
		) z
GROUP BY	z.Lvl

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources