Cross Table with dynamic columns

• Alex-249952

  SSC Rookie

  Points: 40

  More actions

  August 3, 2005 at 9:11 am

  #67460

  I got to change result of one SQL Command like the follow example:

  before ...

  ID year type amt

  -----------------

  7 1999 Mb 23

  8 1999 Ca 44

  9 1999 Si 55

  10 2000 Mb 66

  11 2000 Ca 77

  12 2000 Si 88

  13 1999 Mb 11

  now...

  year Mb Ca Si RowTotal

  ---- -- -- -- ---------

  1999 34 44 55 133

  2000 66 77 88 231

  To do that, I used the SQL command bellow :

  SELECT Pivot_Data.*,

  (Pivot_Data.[1] + Pivot_Data.[2] + Pivot_Data.[3]) AS RowTotal

  FROM (SELECT [year],

  SUM(CASE [type] WHEN 'MB' THEN [amt] ELSE 0 END) AS [Mb],

  SUM(CASE [type] WHEN 'Ca' THEN [amt] ELSE 0 END) AS [Ca],

  SUM(CASE [type] WHEN 'Si' THEN [amt] ELSE 0 END) AS [Si]

  FROM (select * from zzjunk) AS Base_Data

  GROUP BY [year]) AS Pivot_Data

   

  My problem is what I dont have just 3 collumns Mb, CA and Si. The number of collumns depends of another table called ELEMENTS, and tomorrow I can have in this table new elements like ST, VN, etc.

  What is the best way to solve this problem?

  Thanks since now.

  Alex Sandro

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 735712

  More actions

  August 3, 2005 at 10:32 am

  #579230

  Use dynamic SQL to build a string based on the other table, then exec() it.