obychnyy takoy zapros...

Vadim13

Дата: 10.12.2002 23:43:37

Zadacha:

est' tablica (table)
product, buyer, qty
(a1, Vasya, 10)
nuzhno poluchit' otchet v vide tablicy:

product, Vasya, Petya, vsego
a1 10 20 30

est' li sposob uluchshit' predlagaemoe reshenie ?

declare @buyer as varchar (10), @sql as varchar(256), @i as int,
@sql1 as varchar(2048), @sql2 as varchar(1024)
declare cur_buyer cursor local static
for select distinct buyer from test order by buyer

set @i=1
set @sql1='select p.product, total '
set @sql2=' from ##product p '

select product, total=sum(TM) into ##product from table group by product order by product

open cur_buyer
fetch next from cur_buyer into @buyer
while @@fetch_status=0
begin
set @sql='select product, ' + @buyer + '=sum(TM) into ##buyer + CAST(@i as varchar(3)) +
' from test where buyer=''' + @buyer + ''' group by product'
exec (@sql)
set @sql1=@sql1 + ', COALESCE(' + @buyer + ', 0) as ' + @buyer
set @sql2=@sql2 + ' left outer join ##buyer + CAST(@i as varchar(3)) + ' t' + CAST(@i as varchar(3)) +
' on p.product=t' + CAST(@i as varchar(3)) + '.product'
set @i=@i+1
fetch next from cur_buyer into @buyer
end
close cur_buyer
deallocate cur_buyer

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[report]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[report]

set @sql1=@sql1 +'into report ' + @sql2 + ' order by t1.product'
exec (@sql1)

set @i=@i-1
while @i>0
begin
set @sql='drop table ##buyer + CAST(@i as varchar(3))
exec (@sql)
set @i=@i-1
end
drop table ##product

select * from report

tpg

Дата: 11.12.2002 06:49:14

Н-да...
А в таблице только Вася и Петя?
Если так, то всё делается хоть и несовсем простым, но одним запросом.
Например:


SELECT VW1.product, VW1.qty AS Vasya, VW1_1.qty AS Petya, VW1_1.qty+VW1.qty AS vsego

FROM (SELECT product, buyer, SUM(qty) AS qty

             FROM table

             GROUP BY product, buyer) VW1 INNER JOIN

                      VW1 (SELECT product, buyer, SUM(qty) AS qty

             FROM table

             GROUP BY product, buyer) VW1_1 ON VW1.product = VW1_1.product

WHERE     (VW1.buyer = 'Vasya') AND (VW1_1.buyer = 'Petya')

Arch-saint

Дата: 11.12.2002 11:38:10

tt - имя исходной таблицы:

declare @s varchar(8000)
select buyer into ##temp1 from tt group by buyer
select product, SUM(qty) as qty into ##temp3 from tt group by product
select product, buyer, SUM(qty) as qty into ##temp2 from tt group by product, buyer

set @s='select ##temp3.product, '
select @s=@s+buyer+'_tbl.qty as '+buyer+', ' from ##temp1
set @s=@s+'##temp3.qty as vsego into ##temp4 from ##temp3 '
select @s=@s+'INNER JOIN (select product, SUM(qty) as qty FROM ##temp2 WHERE buyer='''+buyer+''' GROUP BY product) as '+buyer+'_tbl ON ##temp3.product='+buyer+'_tbl.product ' FROM ##temp1

EXEC(@s)
select * from ##temp4

drop table ##temp1, ##temp2, ##temp3, ##temp4

Glory

Дата: 11.12.2002 11:55:06

Бросьте вы эти crosstab запросы. Используйте лучше GROUP BY с опцией ROLLUP/CUBE. Жизнь станет гораздо проще.
А пользователи привыкнут (могу подтвердить из личного опыта)

MiCe

Дата: 11.12.2002 19:39:59

когдато RT давал ссылку на эту процу.... источник чес гря не помнь...

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO



CREATE  PROCEDURE crosstab 

@select varchar(8000),

@sumfunc varchar(100), 

@pivot varchar(100), 

@table varchar(1000) 

AS

DECLARE @sql varchar(8000), @delim varchar(1)



SET NOCOUNT ON

SET ANSI_WARNINGS OFF



EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' t WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' t WHERE ' 

+ @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





GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

MiCe

Дата: 11.12.2002 19:52:01

пример пользования...

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

use pubs

GO

DECLARE @RC int

DECLARE @select varchar(8000)

DECLARE @sumfunc varchar(100)

DECLARE @pivot varchar(100)

DECLARE @table varchar(100)

SELECT @select = 'select title from titles inner join sales on (sales.title_id=titles.title_id) group by title'

SELECT @sumfunc = 'sum(qty)'

SELECT @pivot = 'stor_id'

SELECT @table = 'stores'

EXEC  [pubs].[dbo].[crosstab] @select, @sumfunc, @pivot, @table

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

Vadim13

Дата: 12.12.2002 18:32:29

Vsem bol'shoe spasibo.
Mne nravitsya izyaschnyy variant Arch-saint'a (tol'ko zamenil inner join na left outer join da buyer potrimmil :)).