Kreuztabelle - kein Detail- aber SUM Wert

[NormanNG]

Hi,

das mit der temp.Tabelle kann man auch sehr nett mittels einer UDF lösen:

SQL-Code:

CREATE FUNCTION dbo.f_table_of_numbers (@min_number int, @max_number int)
RETURNS @t table (number int)
AS
begin
  declare @number_of_numbers int
  set @number_of_numbers = @max_number - @min_number + 1
  insert into @t ( number ) values (0)

  declare @i int
  set @i = 1
  while @i < @number_of_numbers / 2 + 1
  begin
    insert into @t ( number ) select number + @i from @t
    set @i = @i * 2
  end
  insert into @t ( number ) select number + @i from @t where number + @i < @number_of_numbers
  update @t set number = number + @min_number

  return
end

Dann kann man z.B. die Zahlen 1 bis 12 (Monate) so abrufen:
select * from dbo.f_table_of_numbers(1,12)