put a data into column names?

Question

All,
I'm not sure if this is possible, but I would like to programatically transform part of a table's data into column names. To explain, I have the following table:

river    gaugeLoc       waterflow      class
A               p                  300           1
B               q                  125           1
C               r                  216           1
A               s                   313          2
B               t                    310          2
C              u                    111          2


I'd like to have a table where the classes are given as column names, like this

river   class 1 flow    class 2 flow
A              300               313
B              125               310
C              216               111       
            

Is there a simple way of doing this?

Any help appreciated.

Answer 1

This should do it:

SELECT
   River
  ,Class1Flow=MIN(CASE WHEN Class=1 THEN WaterFlow END)
  ,Class2Flow=MIN(CASE WHEN Class=2 THEN WaterFlow END)
FROM
  MyTable
GROUP BY
  River

Or you can use the PIVOT operator (SQL2005 or above only):

SELECT
   River
  ,Class1Flow=[1]
  ,Class2Flow=[2]
FROM
  MyTable
PIVOT
  (MIN(WaterFlow) FOR Class IN ([1],[2])) p

---

--Brad (My Blog)

Answer 2

FINALLY I got to understand and use pivot tables....
Thanks heaps