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:

• QA vs VS2003. Debugging stored procedure.
• Year calendar 2004
• Yukon 2005 and Whidbey 2005
• Optional parameters in T-SQL: COALESCE vs. ISNULL vs. IS NULL OR
• MSSQL Reset Identity: TRUNCATE TABLE vs. DBCC CHECKIDENT RESEED
• Why app roles are bad in MSSQL