T-SQL (ADP) a kwerenda krzyżowa

[Krzysztof Naworyta]

Oczywiście wiem, że w MS SQL nie ma kwerendy krzyżowej.

Pytanie więc jak _najefektywniej_ ją zasymulować ?

Sumy warunkowe - oczywiście !

Ale co, jeśli ilość kolumn musi być wyliczana dynamicznie i jest zmienna ?

Póki co robię to w procedurze przechowywanej następująco:

- agreguję wartości (nagłówki kolumn) do tmp1
- buduję tmp2 z na zapas ustaloną liczbą kolumn (20)
- INSERT'em i kolejnymi UPDATE'ami wstawiam sumy warunkowe do kolejnych
kolumn (parametr pobierany w pętli z tmp1)

Nie podoba mi się ani owa graniczna ilość kolumn, ani te inserty ...

Macie jakieś pomysły i doświadczenia ?

[Grzegorz Danowski]

Oczywiœcie wiem, że w MS SQL nie ma kwerendy krzyżowej.
Pytanie więc jak _najefektywniej_ jš zasymulować ?
Sumy warunkowe - oczywiœcie !
Ale co, jeœli iloœć kolumn musi być wyliczana dynamicznie i jest zmienna ?

(...)

Spróbuj:
http://www.sqlteam.com/item.asp?ItemID=2955

Grzegorz
Ps. W SQL 2005 będą kwerendy krzyżowe!

[Piglet]

Oczywiœcie wiem, że w MS SQL nie ma kwerendy krzyżowej.
Pytanie więc jak _najefektywniej_ jš zasymulować ?

Witam,

Nie tak dawno wypytywalem o to samo i dostalem od jednego z grupowiczow
procedure skladowana, ktora sie sprawuje bardzo dobrze
Moze pomoze...


CREATE PROC dbo.p_PivotTable
@Table VARCHAR(255),
@RowFld VARCHAR(255),
@ColFld VARCHAR(255),
@ValFld VARCHAR(255),
@Filter VARCHAR(255),
@AggFunct VARCHAR(255)
AS
BEGIN
DECLARE @SQL AS VARCHAR(8000)
DECLARE @CursorSQL AS VARCHAR(8000)
DECLARE @ColVal AS VARCHAR(1024)
DECLARE @NewCol AS VARCHAR(2048)

/* get col hdrs */
SET @CursorSQL = 'DECLARE FldCursor CURSOR FAST_FORWARD FOR '
+ 'SELECT ' + @ColFld + ' FROM ' + @Table + ' GROUP BY '
+ @ColFld + ' ORDER BY ' + @ColFld
EXEC( @CursorSQL )

OPEN FldCursor

/* traverse col hdrs, generating SQL for table */
SET @SQL = 'SELECT ' + @RowFld

FETCH NEXT FROM FldCursor INTO @ColVal
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColVal IS NULL
--Print 'puste'
SET @NewCol = ', ' + @AggFunct + '(CASE WHEN ' + @ColFld + ' IS
NULL THEN ' + @ValFld + ' ELSE NULL END) AS [NULL]'
ELSE
SET @NewCol = ', ' + @AggFunct + '(CASE ' + @ColFld + ' WHEN '''
+ @ColVal + ''' THEN ' + @ValFld + ' ELSE NULL END) AS [' + @ColVal + ']'

SET @SQL = @SQL + @NewCol
FETCH NEXT FROM FldCursor INTO @ColVal
END
CLOSE FldCursor
DEALLOCATE FldCursor

/* finish SQL */
SET @SQL = @SQL + ' FROM ' + @Table
IF 0 < LEN( @Filter)
SET @SQL = @SQL + ' WHERE ' + @Filter
SET @SQL = @SQL + ' GROUP BY ' + @RowFld + ' ORDER BY ' + @RowFld

/* run it! */
-- PRINT @SQL
EXEC (@SQL)
END

GO


z przykladowym wywolaniem:
Exec p_PivotTable
@Table = 'Tabela',
@RowFld = 'Miesiac',
@ColFld = 'TypKlienta',
@ValFld = 'WartoscSprzedazy',
@Filter = '',
@AggFunct = 'sum'

[RAdek]

CZeść

Post by Krzysztof Naworyta
Oczywiście wiem, że w MS SQL nie ma kwerendy krzyżowej.
Pytanie więc jak _najefektywniej_ ją zasymulować ?
- agreguję wartości (nagłówki kolumn) do tmp1
- buduję tmp2 z na zapas ustaloną liczbą kolumn (20)
- INSERT'em i kolejnymi UPDATE'ami wstawiam sumy warunkowe do kolejnych
kolumn (parametr pobierany w pętli z tmp1)
Nie podoba mi się ani owa graniczna ilość kolumn, ani te inserty ...
Macie jakieś pomysły i doświadczenia ?

Tak jak Grzegorz: dynamiczne składanie SQL'a. To co mi się udało
wygrzebać do tej pory na internecie i sprowadzić do uproszczonej
postaci zamieszczam poniżej. Mam jeszcze wersję oryginalną tej
procedury. Z tego co pamiętam to tu walczyłem z Nullami, żeby je
dopasować do moich potrzeb.

============= Początek =================

CREATE PROC dbo.rp_PivotTableSimple
@Table VARCHAR(255),
@RowFld VARCHAR(255),
@ColFld VARCHAR(255),
@ValFld VARCHAR(255),
@Filter VARCHAR(255),
@AggFunct VARCHAR(255)
AS
BEGIN
DECLARE @SQL AS VARCHAR(8000)
DECLARE @CursorSQL AS VARCHAR(8000)
DECLARE @ColVal AS VARCHAR(1024)
DECLARE @NewCol AS VARCHAR(2048)

/* get col hdrs */
SET @CursorSQL = 'DECLARE FldCursor CURSOR FAST_FORWARD FOR '
+ 'SELECT ' + @ColFld + ' FROM ' + @Table + ' GROUP BY '
+ @ColFld + ' ORDER BY ' + @ColFld
EXEC( @CursorSQL )

OPEN FldCursor

/* traverse col hdrs, generating SQL for table */
SET @SQL = 'SELECT ' + @RowFld

FETCH NEXT FROM FldCursor INTO @ColVal
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColVal IS NULL
--Print 'puste'
SET @NewCol = ', ' + @AggFunct + '(CASE WHEN ' + @ColFld + ' IS NULL THEN ' + @ValFld + ' ELSE NULL END) AS [NULL]'
ELSE
SET @NewCol = ', ' + @AggFunct + '(CASE ' + @ColFld + ' WHEN ''' + @ColVal + ''' THEN ' + @ValFld + ' ELSE NULL END) AS [' + @ColVal + ']'

SET @SQL = @SQL + @NewCol
FETCH NEXT FROM FldCursor INTO @ColVal
END
CLOSE FldCursor
DEALLOCATE FldCursor

/* finish SQL */
SET @SQL = @SQL + ' FROM ' + @Table
IF 0 < LEN( @Filter)
SET @SQL = @SQL + ' WHERE ' + @Filter
SET @SQL = @SQL + ' GROUP BY ' + @RowFld + ' ORDER BY ' + @RowFld

/* run it! */
-- PRINT @SQL
EXEC (@SQL)
END
================== Koniec ======================