Transpose Data - 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 2012 Forums

Transact-SQL (2012)

Transpose Data

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2014-09-30 : 04:19:59

Hi,

I need some help on transposing some data.

I need to transpose records from a table.

From


Name	Score	ExamName
------------------------
John	53	English
John	98	Mathematics
John	78	Science
Peter	87	English
Peter	70	Mathematics
Peter	82	Science
Jane	98	English
Jane	91	Mathematics
Jane	86	Science

and transpose it to


Name	English		Mathematics	Science
-----------------------------------------------
John	53		98		78
Peter	87		70		82
Jane	98		91		86

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-30 : 04:22:56

[code]
select * from someTable
pivot
(
max(score) for ExamName in (English,Mathematics,Science)
) as t[/code]

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

hayashiryo
Starting Member

46 Posts

Posted - 2014-09-30 : 04:54:40

Hi Harsh. I tried your solution. But couldn't get it work.

Note: My table has other values as well. Not just name, score and ExamName. For instance it also has StudID.

In the end, it only showed the 3 columns (English, Mathematics, Science) as NULL

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-30 : 05:14:13

Works perfectly for me:


declare @a table
(
Name varchar(20),
Score	int,
ExamName varchar(20)
)

insert @a
select 'John',53,'English' union all
select 'John',98,'Mathematics' union all
select 'John',78,'Science' union all
select 'Peter',87,'English' union all
select 'Peter',70,'Mathematics' union all
select 'Peter',82,'Science' union all
select 'Jane',98,'English' union all
select 'Jane',91,'Mathematics' union all
select 'Jane',86,'Science'

select * from @a
pivot
(max(score) for ExamName in (English,Mathematics,Science)
) as t

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

hayashiryo
Starting Member

46 Posts

Posted - 2014-09-30 : 06:28:27

I tried your last code, and yes, it does work perfectly.

Is it because of my table structure?

I tried to modify your last code. Instead of hardcoding the select statement (ex. select 'John',53,'English' union all), i wrote a select statement to extract the data out from my tables.

Still it gives a NULL value. I'm perplexed!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-09-30 : 08:37:33

Have you used this code?

select * from your_table
pivot
(max(score) for ExamName in (English,Mathematics,Science)
) as t

Madhivanan

Failing to plan is Planning to fail

hayashiryo
Starting Member

46 Posts

Posted - 2014-09-30 : 21:46:10

Hi madhivanan.

I tried what you wrote. I kept my query to just 1 table.

And still my score shows as NULL.

My Score field type is set as Decimal(10,2)

Is it because it's of decimal value?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-01 : 01:19:48

Please post actual data along with table definitions and the query you tried.
May be something is not right somewhere in the data or table definition.

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

hayashiryo
Starting Member

46 Posts

Posted - 2014-10-01 : 03:20:44

Hi guys. I need a slap to my forehead.

It's because of type error when listing the columns. It works nicely now. Thanks for all the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources