Cross tab help (or something)

• Robnjay

  Grasshopper

  Points: 13

  More actions

  October 10, 2006 at 2:27 pm

  #91875

  I am working with MS SQL Server (2000), and I have a table that looks something like this:

  Meter_Name	Local_Meter_ID	Daily_Hours	Integrator_Value	Reading_Date	Legacy_ID
  A	Area1	24	712	10/10/2006	25A
  B	Area2	24	488	10/10/2006	25B
  C	Area3	24	644	10/10/2006	42C
  D	Area4	24	512	10/10/2006	54D
  E	Area5	24	256	10/10/2006	62E
  F	Area6	24	788	10/10/2006	71F
  A	Area1	16	728	10/9/2006	25A
  B	Area2	20	508	10/9/2006	25B
  C	Area3	22	666	10/9/2006	42C
  D	Area4	24	536	10/9/2006	54D
  E	Area5	24	280	10/9/2006	62E
  F	Area6	24	812	10/9/2006	71F

   

  I would like to run a query which results in something like this:

  Meter_Name	Local_Meter_ID	Legacy_ID	Daily_Hours 10/10/2006	Integrator_Hours 10/10/2006	Daily_Hours 10/9/2006	Integrator_Hours 10/9/2006
  A	Area1	25A	24	712	16	728
  B	Area2	25B	24	488	20	508
  C	Area3	42C	24	644	22	666
  D	Area4	54D	24	512	24	536
  E	Area5	62E	24	256	24	280
  F	Area6	71F	24	788	24	812

   

  If I knew how to create field names from selected data on the fly, I might have gotten this. 

  Incidentally, there will be 90 days of "history" arranged to the right of each meter in the finished product.

  Thanks

  Rob

• erde

  SSC Enthusiast

  Points: 123

  More actions

  October 11, 2006 at 8:57 am

  #665244

  Perhaps you can do something with this. You can find the whole thread on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216.

  CREATE PROCEDURE crosstab 
  @select varchar(8000),
  @sumfunc varchar(100), 
  @pivot varchar(100), 
  @table varchar(100),
  @where varchar(1000)=null
  AS
  
  DECLARE @sql varchar(8000), @delim varchar(1)
  SET NOCOUNT ON
  SET ANSI_WARNINGS OFF
  
  EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
  EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
  + IsNull(@where,'1=1') + ' AND ' + @pivot + ' Is Not Null')
  
  SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
  
  SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
  WHEN 0 THEN '' ELSE '''' END 
  FROM tempdb.information_schema.columns 
  WHERE table_name='##pivot' AND column_name='pivot'
  
  SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
  stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
  + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
  
  DROP TABLE ##pivot
  
  SELECT @sql=left(@sql, len(@sql)-1)
  SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
  
  EXEC (@select)
  SET ANSI_WARNINGS ON

   

  ---

  Kindest Regards,

  erde