Anatoly Lubarsky Logo
programming, design, integration, games, music

How to PIVOT Tables in MSSQL 2000

Question from the forums:

"so i have 2 prices from the same furnisher and i would like to show those for each year as we get each year new prices... right now i have a table where all these items are on 1 and the same row (furnishername, year, price1, price2) i would like to get that into 3 columns (furnisher, price1_year, price2_year) thnx a lot."


Yes, you can pivot tables easily in MSSQL 2005 (aka yukon) easily but how can we do it in MSSQL 2000 ? Here is the answer:


select t.myid as furnisher
      ,t1.myprice as price1_year
      ,t2.myprice price2_year
  from (select distinct myid from mytable) t 
  join (select myid
              ,myprice
              ,rownum = (select count(*) 
                           from mytable t2 
                          where t2.myid = t1.myid 
                            and t2.id <= t1.id)
          from mytable t1) t1 
    on t.myid = t1.myid and t1.rownum = 1
  join (select myid
              ,myprice
              ,rownum = (select count(*) 
                           from mytable t2
                          where t2.myid = t1.myid 
                            and t2.id <= t1.id)
          from mytable t1) t2 
    on t.myid = t2.myid and t2.rownum = 2
 order by 1 

Enjoy


Related Posts:

Friday, April 02, 2004 11:50 PM

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs