selecting columns and rows from pivot table

Question

selecting columns and rows from pivot table

barlet

SSC Rookie

Points: 36
More actions
June 3, 2017 at 1:51 pm

#321933

I have this table previously saved to the db from a pivot result
baseyear__year1__count1__year2__count2__year3__count3__year4__count4__year5__count5
2012_____2012______10___2013______9___2014______7___2015_____5___2016______4
2013________0_______0___2013_____20___2014_____18___2015____15___2016_____10
2014________0_______0______0______0___2014_____25___2015____23___2016_____20
2015________0_______0______0______0______0______0___2015____20___2016_____19
2016________0_______0______0______0______0______0______0_____0___2016_____28
How could I obtain this result
baseyear__year1__count1__year2__count2__year3__count3
2014_____2014______25___2015____23___2016_____20
2015________0_______0___2015____20___2016_____19
2016________0_______0______0_____0___2016_____28

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004679 More actions · Answer 1

It's easy. Just rename (re-alias) the column names and limit the rows with a WHERE clause. Like this.

SELECT year1 = year3 ,count1 = count3 ,year2 = year4 ,count2 = count4 ,year3 = year5 ,count3 = count5 FROM dbo.YourTable WHERE baseyear >= 2014 ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)