Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Re-arrange columns after pivot
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

808 Posts

Posted - 10/01/2013 :  16:05:50  Show Profile  Reply with Quote
Hi,

Could someone give me a little help please. I am workong on this Pivot select. At it is last part, I want to find a way to re-arrange the column order.

Here is the test data:

if OBJECT_ID ('tempdb..#temp') is not null drop table #temp
go


create table #temp(b_date varchar(15), Gender varchar(5), Admin_date varchar(15), S_ID int, TEST_NAME varchar(5), SCHOOL_YEAR varchar(5), PART_DESCRIPTION varchar(15),Test_Score int)

Insert #temp Values ('1996-01-27','M ','2013-02-09',123,'ACT','2008 ','Reading', 30)
Insert #temp Values ('1996-01-27','M ','2013-02-09',123,'ACT','2008 ','Math', 36)
Insert #temp Values ('1996-01-27','M ','2013-02-09',123,'ACT','2008 ','Writing', 1)
Insert #temp Values ('1996-01-27','M ','2013-02-09',123,'ACT','2008 ','English', 35)
Insert #temp Values ('1996-01-27','M ','2013-02-09',123,'ACT','2008 ','Composite', 34)
Insert #temp Values ('1996-01-27','M ','2013-02-09',123,'ACT','2008 ','Science', 36)
Insert #temp Values ('1996-01-27','M ','2012-11-28',123,'ACT','2008 ','Reading', 34)
Insert #temp Values ('1996-01-27','M ','2012-11-28',123,'ACT','2008 ','Math', 36)
Insert #temp Values ('1996-01-27','M ','2012-11-28',123,'ACT','2008 ','Writing', 8)
Insert #temp Values ('1996-01-27','M ','2012-11-28',123,'ACT','2008 ','English', 36)
Insert #temp Values ('1996-01-27','M ','2012-11-28',123,'ACT','2008 ','Composite', 36)
Insert #temp Values ('1996-01-27','M ','2012-11-28',123,'ACT','2008 ','Science', 36)
Insert #temp Values ('1996-02-27','F ','2013-02-09',456,'ACT','2008 ','Reading', 23)
Insert #temp Values ('1996-02-27','F ','2013-02-09',456,'ACT','2008 ','Math', 19)
Insert #temp Values ('1996-02-27','F ','2013-02-09',456,'ACT','2008 ','Writing', 2)
Insert #temp Values ('1996-02-27','F ','2013-02-09',456,'ACT','2008 ','English', 22)
Insert #temp Values ('1996-02-27','F ','2013-02-09',456,'ACT','2008 ','Composite', 22)
Insert #temp Values ('1996-02-27','F ','2013-02-09',456,'ACT','2008 ','Science', 23)

this returns the pivot data:
Select * from #temp
pivot
(sum(TEST_SCORE)
for PART_DESCRIPTION in ([Reading], [Math], [Writing], [English], [Composite], [Science])
) as pvt

But the output requries the last couple columns to be SCHOOL_YEAR, TEST_NAME, i.e. they come after the scores.

I thought the easiest way will be to select into #temp2, then extract from there. Even that, I couldn't get that to work with the select pivot.

Thanks!

Edited by - Hommer on 10/01/2013 16:34:49

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/01/2013 :  16:13:17  Show Profile  Reply with Quote
You just specify the column in the order you want instead of *.
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 10/01/2013 :  16:25:04  Show Profile  Reply with Quote
where do I specify the columns come after the SUM()?

This next wouldn't work:

Select b_date, Gender, s_id from #temp
pivot
(sum(TEST_SCORE), test_name
for PART_DESCRIPTION in ([Reading], [Math], [Writing], [English], [Composite], [Science])
) as pvt

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 10/01/2013 :  16:48:03  Show Profile  Reply with Quote
Run the code with the SELECT *, look at the column names, and then replace the "*" with the column names in any order that you want them to appear - like shown below:
Select b_date,Gender,Admin_date, S_ID,reading, Math, Writing,English, Composite, Science,
	TEST_NAME, SCHOOL_YEAR    from #temp 
pivot
(sum(TEST_SCORE)
for PART_DESCRIPTION in ([Reading], [Math], [Writing], [English], [Composite], [Science])
) as pvt
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 10/01/2013 :  16:55:40  Show Profile  Reply with Quote
I see. I have to repeat those columns twice.

Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000