перекрестные SQL-запросы

← →
mcmax (2003-05-14 16:08) [0]

Подскажите плз, как можно ли в Oracle выполнить перекрестный запрос с группировкой?
В Jet SQL это делается при помощи ключевых слов Transform и Pivot, а вот как в Oracle - не знаю?

← →
Stas © (2003-05-14 16:13) [1]

Есть х.п. под msSQl если разберешься переделаешь на свой сервак.

← →
mcmax (2003-05-14 16:40) [2]

Не совсем понял что есть х.п. ?
Если пример то кидай, попробую разобраться...

← →
Stas © (2003-05-14 16:49) [3]

х.п.-Хранимая процедура (Stored Procedure).

CREATE PROC sp_CrossTabDp
@table AS sysname,
@onrows AS nvarchar(128),
@onrowsalias AS sysname = NULL,
@oncols AS nvarchar(256),
@sumcol AS sysname = NULL ,
@dated as nvarchar(10),
@dateu as nvarchar(10)
AS

DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

SET @sql =
"SELECT" + @NEWLINE +
" " + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN " AS " + @onrowsalias
ELSE ""
END
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
"INSERT INTO #keys " +
"SELECT DISTINCT CAST(" +@oncols + " AS nvarchar(100)) " +
"FROM " + @table

EXEC (@keyssql)

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + "," + @NEWLINE +
" SUM(CASE CAST(" + @oncols +
" AS NVARCHAR(100))" + @NEWLINE +
" WHEN N""" + @key +
""" THEN " + @sumcol+ @NEWLINE +
" ELSE NULL" + @NEWLINE +
" END) AS " + @key

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

SET @sql = @sql + @NEWLINE +
"FROM " + @table + @NEWLINE +
"WHERE "+ @sumcol+">="+@DATED+" AND "+@sumcol+"<="+@dateu +@NEWLINE+
"GROUP BY " + @onrows + @NEWLINE +
"ORDER BY " + @onrows

--PRINT @sql + @NEWLINE--
EXEC (@sql)
GO

Вызываешь ее таким образом:
sp_CrossTabDp
@table = "MyTable",
@onrows = "MyFldRow"
@oncols = "MyCols",
@sumcol = "RESULT"

Где MyTable - таблица с которой берешь данные
MyFldRow - Колонки, данные которых будут строками (через запятую)
MyCols - Имя колонки данные которой будут колонками
RESULT Имя колонки данные которой будут на пересечении (Здесь SUM если разберешься можно max,min и т.д)

← →
Stas © (2003-05-14 16:53) [4]

Извеняюсь!!(Предидущая не верная)

Вот Так

CREATE PROC sp_CrossTab
@table AS sysname,
@onrows AS nvarchar(128),
@onrowsalias AS sysname = NULL,
@oncols AS nvarchar(256),
@sumcol AS sysname = NULL

AS

DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

SET @sql =
"SELECT" + @NEWLINE +
" " + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN " AS " + @onrowsalias
ELSE ""
END
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
"INSERT INTO #keys " +
"SELECT DISTINCT CAST(" +@oncols + " AS nvarchar(100)) " +
"FROM " + @table

EXEC (@keyssql)

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + "," + @NEWLINE +
" SUM(CASE CAST(" + @oncols +
" AS NVARCHAR(100))" + @NEWLINE +
" WHEN N""" + @key +
""" THEN " + @sumcol+ @NEWLINE +
" ELSE NULL" + @NEWLINE +
" END) AS " + @key

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

SET @sql = @sql + @NEWLINE +
"FROM " + @table + @NEWLINE +
"GROUP BY " + @onrows + @NEWLINE +
"ORDER BY " + @onrows

--PRINT @sql + @NEWLINE--
EXEC (@sql)
GO

Вызываешь ее таким образом:
sp_CrossTab
@table = "MyTable",
@onrows = "MyFldRow"
@oncols = "MyCols",
@sumcol = "RESULT"

Где MyTable - таблица с которой берешь данные
MyFldRow - Колонки, данные которых будут строками (через запятую)
MyCols - Имя колонки данные которой будут колонками
RESULT Имя колонки данные которой будут на пересечении (Здесь SUM если разберешься можно max,min и т.д)

← →
mcmax (2003-05-14 16:55) [5]

Выглядит страшно :))
Спасибо, пошел вникать...

← →
mcmax (2003-05-15 11:41) [6]

не совсем понятен следующий кусок кода

SET @sql = @sql + "," + @NEWLINE +
" SUM(CASE CAST(" + @oncols +
" AS NVARCHAR(100))" + @NEWLINE +
" WHEN N""" + @key +
""" THEN " + @sumcol+ @NEWLINE +
" ELSE NULL" + @NEWLINE +
" END) AS " + @key

Особенное смущение вызывают оператор Case, When N(что есть N?)?
Вобщем если можешь закоментируй этот кусок, что б до меня дошла логика или кинь пример уже сформированной строки @sql

Заранее спасибо

← →
mcmax (2003-05-15 15:27) [7]

Спасибо уже разобрался...

перекрестные SQL-запросы Найти похожие ветки