How to convert Rows into Columns

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

Transact-SQL (2000)

How to convert Rows into Columns

Author

Topic

gnk
Starting Member

1 Post

Posted - 2003-02-11 : 06:39:58

Hi,

I want to show the rows in one result set as columns of another result set. How can I achieve this?

I have searched the forums but couldn't get the exact solution...
Thanks in advance for the help.

E.g: Lets look at a simple example of 3 tables for exactly what I want.

Students Table:
studentID studentName
----------------------
1   John
2   Robert
3   James

Subjects Table:
SubjectID subjectName
---------------------------
101   Mathematics
102   Physics
103   Chemistry

Marks Table:
StudentID    SubjectID    Marks
--------------------------------------
1    101    95
1    102    90
1    103    85
2    101    90
2    102    85
2    103    85
3    101    92
3    102    80
3    103    80

I want to show the Final result as :
StudentName    Mathematics   Physics    Chemistry
---------------------------------------------------------
John     95    90    85
Robert   90    85    85
James    92    80    80

slacker
Posting Yak Master

115 Posts

Posted - 2003-02-11 : 18:56:55

The only way I know is with dynamic sql and cursors... For this type of situation... I would say to try to avoid listing it like this... these types of situations would be better listing down...

ie..
Student:

Mathematics 90
science 80 etc...

/* code. Use at your own risk. It has a cursor. */
/* Note i used inner joins here... you probably will want to have it switched to left outer joins... So that students who dont have a subject wont get excluded. Also for performance you may want to switch to sp_executesql. But the main problem is the cursor.. */

create procedure GetMarks (
@StudentID INT = NULL -- if you want a particular student
)
AS

DECLARE @SelectClause VARCHAR(2000) -- to hold select clause
DECLARE @FromClause VARCHAR(2000) -- to hold from clause
DECLARE @FullSql VARCHAR(4000) -- holds full sql statement
-- create cursor with subjects
DECLARE SubjectCur CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT SubjectID,SubjectName FROM Subjects

DECLARE @SubjectName varchar(100)
DECLARE @SubjectID INT
OPEN SubjectCur

-- initialize select clause
SELECT @SelectClause = 'SELECT Students.StudentID,Students.StudentName'
-- initialize from clause
SELECT @FromClause = 'FROM Students'

-- iterate through subjects building select and from clause
FETCH NEXT from SubjectCur INTO @SubjectID,@SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
-- do select clause
SELECT @SelectClause = @SelectClause + ',' + @SubjectName + '.Marks AS ' + @SubjectName
-- do from clause
SELECT @FromClause = @FromClause + ' '
SELECT @FromClause = @FromClause + 'INNER JOIN Marks ' + @SubjectName + ' ON (' + @SubjectName + '.StudentID=Students.StudentID AND ' + @SubjectName + '.SubjectID=' + STR(@SubjectID) + ') '
FETCH NEXT from SubjectCur INTO @SubjectID,@SubjectName
END

-- COMBINE THE CLAUSES
SELECT @FullSql = @SelectClause + ' ' + @FromClause

-- add the optional student id
IF (@studentID is NOT NULL)
SELECT @FullSql = @FullSql + ' WHERE Students.StudentID=' + STR(@StudentID)

-- execute the sql
exec (@FullSql)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-11 : 19:10:17

There is a method that does not use a cursor:

http://www.sqlteam.com/item.asp?ItemID=2955

Otherwise it's very similar to what's already posted.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-02-11 : 19:51:20

Follow robvolk's lead: but just for the mental exercise...you can do this - (I cheated by creating a view):

create view vtempmarks
as
select a.studentname, c.subjectName, b.marks
from Students a inner join Marks b
on a.studentid = b.studentid
inner join Subjects c
on b.subjectid = c.subjectid
go

create table #results (Studentname nvarchar(50))
create table #subjects (Subjectname nvarchar(50))
declare @subjectname nvarchar(50)
declare @studentname nvarchar(50)
declare @sql nvarchar(1000)

--build result table
select top 1 @subjectname = subjectname from Subjects order by subjectid
while (@subjectname not in (select subjectname from #subjects))
begin

set @sql = 'ALTER TABLE #results add ' + @subjectname + ' int'
exec (@sql)
insert into #subjects (subjectname) select @subjectname
select top 1 @subjectname = subjectname from subjects where subjectname not in (select subjectname from #subjects) order by subjectid
end

--now comile marks
select top 1 @studentname = studentname from Students order by studentid
while (@studentname not in (select studentname from #results))
begin

insert into #results (studentname) select @studentname

delete from #subjects
select top 1 @subjectname = subjectname from Subjects order by subjectid

while (@subjectname not in (select subjectname from #subjects))
begin
insert into #subjects (subjectname) select @subjectname
set @sql = 'update #results set ' + @subjectname + ' = (select top 1 marks from vtempmarks '
set @sql = @sql + 'where studentname = ' + '''' + @studentname + '''' + ' and subjectname = '
set @sql = @sql + '''' + @subjectname + '''' + ') where studentname = ' + '''' + @studentname + ''''
exec (@sql)
print @sql
select top 1 @subjectname = subjectname from subjects where subjectname not in (select subjectname from #subjects) order by subjectid
end

select top 1 @studentname =studentname from students where studentname not in (select studentname from #results) order by studentid
end
select * from #results

drop table #results
drop table #subjects
drop view vtempmarks

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

slacker
Posting Yak Master

115 Posts

Posted - 2003-02-11 : 20:23:33

neat :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources