Changing Rows into Columns - SQL Server Forums

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)

Changing Rows into Columns

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-04-21 : 12:18:32

I have a table which has the following columns.

CREATE TABLE [dbo].[test_Exams] (
[ExamID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamTypeID] [int] NOT NULL ,
[Title] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Duration] [int] NOT NULL ,
[TotalQuestions] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateModified] [datetime] NOT NULL ,
[ExamStatusID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And another table which is called test_ExamTypes

CREATE TABLE [dbo].[test_ExamTypes] (
[ExamTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now, when I select join the tables test_Exams and test_ExamTypes I get the following result.

Score Name
1 AB1
2.5 AB2

As, you can see that the Name AB1, AB2 which is ExamType is displayed in different rows which means that if I am displaying some student records it will be spanned into multiple rows. How can I change this to look in a single row. Something like this.

AB1 AB2
1 2.5

The number of columns which is AB1, AB2 are not fixed they can be any number of columns.

Any ideas!

THanks a lot!

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-04-21 : 15:26:09

I got the answer using the PIVOT TABLES.

Thanks

Mohammad Azam
www.azamsharp.net

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-04-22 : 04:45:42

Can u please describe the solution i have a problem of similar kind.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-22 : 05:00:30

check out this link ..

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

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-04-24 : 03:02:36

Thanx that works fine.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 03:58:59

Also refer Cross-Tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources