SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

770 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

4360 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

770 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

3326 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

770 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000