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/
Go to Top of Page

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



Go to Top of Page

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/
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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/
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -