Header Row to Column - 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

General SQL Server Forums

New to SQL Server Programming

Header Row to Column

Author

Topic

anujpratap09
Starting Member

5 Posts

Posted - 2010-06-22 : 06:42:42

Hi All,

I am struck in a problem...
I want to change my header row to in column...

Here is my table

Phy Chem Math
100 200 300

First row is table header
i want to convert into given format:

Phy 100
Chem 200
Math 300

Thanks in advance

Anuj Pratap Singh

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 07:11:34

If using SQL 2005



select 
* from 
(select * from yourtable)u
unpivot
(marks for subjects in (phy,chem,math))v

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 07:14:50

Also
Is this a howework question?I hope it is.
But if it is a live project then you surely you need to learn more on database normalisation.Especially 1NF as your design is breaking the rules of 1NF form.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-06-22 : 07:23:27

Hi,

This can be easily done using pivot Unpivot. Kindly go through the article http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx you can resolve your problem.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

anujpratap09
Starting Member

5 Posts

Posted - 2010-06-22 : 07:25:40

quote:
Originally posted by Idera

If using SQL 2005

select
* from
(select * from yourtable)u
unpivot
(marks for subjects in (phy,chem,math))v

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Thanks for reply....
Here is my table structure
CREATE TABLE [dbo].[tbl_test_info](
[TestInfoId] [int] IDENTITY(1,1) NOT NULL,
[CenterCode] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TestID] [tinyint] NOT NULL,
[TestNumber] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TestDate] [datetime] NOT NULL,
[TotalQuestion] [int] NULL,
[PhyMaxMarks] [int] NULL,
[CheMaxMarks] [int] NULL,
[MathMaxMarks] [int] NULL,
[Easy] [int] NULL,
[Medium] [int] NULL,
[Tough] [int] NULL,
[TotalStudent] [int] NULL,
CONSTRAINT [PK_tbl_test_info] PRIMARY KEY CLUSTERED
(
[TestInfoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I want PhyMaxMarks,CheMaxMarks,MathMaxMarks in different rows..
Currently all three data showing in a single row..

Anuj Pratap Singh

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 07:33:21

Post some sample data & expected output.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

anujpratap09
Starting Member

5 Posts

Posted - 2010-06-22 : 07:41:10

quote:
Originally posted by Idera

Post some sample data & expected output.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Here is sample data:

TestInfoId CenterCode TotalQuestion PhyMaxMarks CheMaxMarks MathMarks
1 05 50 100 150 200

I need this single row output in given format

Subject MaxMarks
PhyMaxMarks 100
CheMaxMarks 150
MathMarks 200

Anuj Pratap Singh

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 07:46:13

Did you try my solution?


declare @tbl as table(PhyMaxMarks int,CheMaxMarks int,MathMarks int)
insert into @tbl
select 100,150,200

select 
Subject,MaxMarks from 
(select * from @tbl)u
unpivot
(MaxMarks for Subject  in (PhyMaxMarks,CheMaxMarks,MathMarks))v


-- OUTPUT

Subject	      MaxMarks
PhyMaxMarks	100
CheMaxMarks	150
MathMarks	200

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

anujpratap09
Starting Member

5 Posts

Posted - 2010-06-22 : 07:57:49

quote:
Originally posted by Idera

Did you try my solution?

declare @tbl as table(PhyMaxMarks int,CheMaxMarks int,MathMarks int)
insert into @tbl
select 100,150,200

select
Subject,MaxMarks from
(select * from @tbl)u
unpivot
(MaxMarks for Subject in (PhyMaxMarks,CheMaxMarks,MathMarks))v

-- OUTPUT

Subject MaxMarks
PhyMaxMarks 100
CheMaxMarks 150
MathMarks 200

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Thanks alot
It's working for me..............

Anuj Pratap Singh

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 08:06:35

Welcome.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources