Re-arrange columns after pivot

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

Re-arrange columns after pivot

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-01 : 16:05:50

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!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-01 : 16:13:17

You just specify the column in the order you want instead of *.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-01 : 16:25:04

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-01 : 16:48:03

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-01 : 16:55:40

I see. I have to repeat those columns twice.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources