Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

возможно ли динамическое колличесто полей в SQL запросе

aGo © (2002-09-23 17:10:00)
Подскажите, пожалуйста, есть ли возможность с помощью SQL сформировать запрос из главной и подчиненной табличек, но таким образом, чтобы в результирующем наборе данных колличество полей зависело от колличества записей в подчиненной табличке ??

Если возможно, то подскажите какими способами !!!

ЗАранее огромнейшее спасибо ;)

kaif (2002-09-23 23:18:00) [1]
А почему бы не подумать о том, чтобы денормализацию на клиенте делать? Мне кажется, что утяжеление SQL не всегда оправдано.
Если клиент написан на Delphi, то можно при помощи компонента XLReport от Afalina Soft (или руками, если этот компонент не нравится) выбросить такой отчет в Excel.
Это так, в виде идеи...

BlackTiger (2002-09-23 21:19:00) [2]
Моя доработанная версия (может кому пригодится, принимаю предложения по оптимизации, хотя и так довольно быстро работает) - длина конечного запроса до 24000 символов, до 4-х pivot-колонок, можно задать префикс и окончание каждому pivot-столбцу:
---------------------------------------------------
CREATE PROCEDURE sp_create_crosstab(
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@pivot_table_filter varchar(2000),
@field_name_prefix varchar(10),
@field_name_ending varchar(10),
@sumfunc1 varchar(100) = NULL,
@field1_name_prefix varchar(10) = NULL,
@field1_name_ending varchar(10) = NULL,
@sumfunc2 varchar(100) = NULL,
@field2_name_prefix varchar(10) = NULL,
@field2_name_ending varchar(10) = NULL,
@sumfunc3 varchar(100) = NULL,
@field3_name_prefix varchar(10) = NULL,
@field3_name_ending varchar(10) = NULL
)
AS BEGIN
DECLARE @sql varchar(8000), @delim varchar(1)
DECLARE @select1 varchar(8000),@select2 varchar(8000),@select3 varchar(8000)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

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

IF @pivot_table_filter = ""
EXEC ("INSERT INTO ##pivot SELECT DISTINCT " + @pivot + " FROM " + @table + " WHERE "
+ @pivot + " Is Not Null ORDER BY " + @pivot)
ELSE
EXEC ("INSERT INTO ##pivot SELECT DISTINCT " + @pivot + " FROM " + @table + " WHERE ("
+ @pivot + " Is Not Null) AND " + @pivot_table_filter + " ORDER BY " + @pivot)

SELECT @sql="", @sumfunc=STUFF(@sumfunc, LEN(@sumfunc), 1, " ELSE 0 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 + """" + @field_name_prefix + CONVERT(varchar(100), pivot) + @field_name_ending + """ = " +
STUFF(@sumfunc, CHARINDEX( "(", @sumfunc) + 1, 0, " CASE " + @pivot + " WHEN "
+ @delim + CONVERT(varchar(100), pivot) + @delim + " THEN ") + ", "
FROM ##pivot

-- ***********************************************************

IF NOT (@sumfunc1 Is Null)
BEGIN
SELECT @sumfunc1=STUFF(@sumfunc1, LEN(@sumfunc1), 1, " ELSE 0 END)")
SELECT @sql = @sql + """" + @field1_name_prefix + CONVERT(varchar(100), pivot) + @field1_name_ending + """ = " +
STUFF(@sumfunc1, CHARINDEX( "(", @sumfunc1) + 1, 0, " CASE " + @pivot + " WHEN "
+ @delim + CONVERT(varchar(100), pivot) + @delim + " THEN ") + ", "
FROM ##pivot
END

IF NOT (@sumfunc2 Is Null)
BEGIN
SELECT @sumfunc2=STUFF(@sumfunc2, LEN(@sumfunc2), 1, " ELSE 0 END)")
SELECT @sql = @sql + """" + @field2_name_prefix + CONVERT(varchar(100), pivot) + @field2_name_ending + """ = " +
STUFF(@sumfunc2, CHARINDEX( "(", @sumfunc2) + 1, 0, " CASE " + @pivot + " WHEN "
+ @delim + CONVERT(varchar(100), pivot) + @delim + " THEN ") + ", "
FROM ##pivot
END

IF NOT (@sumfunc3 Is Null)
BEGIN
SELECT @sumfunc3=STUFF(@sumfunc3, LEN(@sumfunc3), 1, " ELSE 0 END)")
SELECT @sql = @sql + """" + @field3_name_prefix + CONVERT(varchar(100), pivot) + @field3_name_ending + """ = " +
STUFF(@sumfunc3, CHARINDEX( "(", @sumfunc3) + 1, 0, " CASE " + @pivot + " WHEN "
+ @delim + CONVERT(varchar(100), pivot) + @delim + " THEN ") + ", "
FROM ##pivot
END

-- ***********************************************************

DROP TABLE ##pivot

SELECT @sql=LEFT(@sql, LEN(@sql) - 1)

SELECT @select=STUFF(@select, CHARINDEX(" FROM ", @select) + 1, 0, ", " + @sql + " ")
--PRINT "@select = " + @select
--PRINT "LEN(@select) = " + STR(LEN(@select))

SET @select1 = @select
IF LEN(@select) > 8000
BEGIN
SET @select1 = SUBSTRING(@select,1,8000)
SET @select2 = SUBSTRING(@select,8001,8000)
SET @select3 = SUBSTRING(@select,16001,8000)
END
ELSE
SELECT @select1 = @select

--PRINT "@select1 = " + @select1
--PRINT "@select2 = " + @select2
--PRINT "@select3 = " + @select3
EXEC(@select1 + @select2 + @select3)
SET ANSI_WARNINGS ON
END
---------------------------------------------------

BlackTiger (2002-09-23 21:13:00) [3]
Вот линк на статейку " http://www.sqlteam.com/item.asp?ItemID=2955".
Там есть примеры вызова.
А сам текст такой:
---------------------------------------------------
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
BEGIN
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 "
+ @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
END
---------------------------------------------------
Примеры вызова (задействованы стандартные базы MSSQL "pubs" и "Northwind"):
/* 1 */
EXECUTE crosstab "select title from titles inner join sales on (sales.title_id=titles.title_id)
group by title", "sum(qty)","stor_id","stores"

/* 2 */
EXECUTE crosstab "select pub_name, count(qty) as orders, sum(qty) as total
from sales inner join titles on (sales.title_id=titles.title_id)
right join publishers on (publishers.pub_id=titles.pub_id)
group by pub_name", "sum(qty)","type","titles"

aGo (2002-09-23 19:03:00) [4]
>BlackTiger

да ! если можна то киньте, пожалуйста
aniutka_l@yahoo.com

aGo (2002-09-23 18:34:00) [5]
Огромное Вам спасибо!
Сейчас буду разбираться !

Delirium (2002-09-23 18:25:00) [6]
Да, похоже - но есть отличия, например - порядок колонок iParamPosт.е. колонки формируясь динамически выстраиваются в порядке, не выводящемся в результате

BlackTiger (2002-09-23 18:22:00) [7]
Насколько я понял - это простой "crosstab".
У меня есть текст хранимой процедуры для MSSQL (взято с SQLTeam.com, кое-что изменено), которая на выходе выдает "перевертыш".
Кинуть?

Delirium (2002-09-23 18:08:00) [8]
Рабочий из программы в качестве примера MSSQL
set nocount on -- Получаю общий список денормализованных параметров из Кеша select EO_Cache.iPos, EO_Cache.idMaterial, ER_Materials.cMaterial, EO_Cache_Parameters.idParameter, ER_Parameters.cParameter, EO_Cache_Parameters.cValue, ER_Units.cUnit, ER_Parameters.iPos as iParamPos into #tAgg from EO_Cache join EO_Cache_Parameters on EO_Cache.iPos=EO_Cache_Parameters.iPos join ER_Materials on EO_Cache.idMaterial=ER_Materials.idMaterial join ER_Parameters on EO_Cache_Parameters.idParameter=ER_Parameters.idParameter join ER_Units on ER_Materials.idUnit=ER_Units.idUnit -- Денормализую параметры в колонки -- макс. кол-во параметров declare @MaxParam int select @MaxParam=Count(*) from er_Parameters -- Собираю командную строку declare @CmdLine1 varchar(8000) declare @CmdLine2 varchar(8000) Set @CmdLine1=" select distinct t1.iPos, t1.idMaterial, t1.cMaterial, t1.idParameter as idParam1, t1.cValue as cValue1," Set @CmdLine2=" from (select * from #tAgg where iParamPos=1) t1" declare @i int Set @i=2 while @i<=@MaxParam begin Set @CmdLine1=@CmdLine1+" t"+LTrim(Str(@i))+".idParameter as idParam"+LTrim(Str(@i))+", t"+LTrim(Str(@i))+".cValue as cValue"+LTrim(Str(@i))+"," Set @CmdLine2=@CmdLine2+" left join (select * from #tAgg where iParamPos="+LTrim(Str(@i))+") t"+LTrim(Str(@i))+" on t1.iPos=t"+LTrim(Str(@i))+".iPos" Set @i=@i+1 end Set @CmdLine1=@CmdLine1+" t1.cUnit " exec(@CmdLine1+@CmdLine2+" order by t1.cMaterial") drop table #tAgg

aGo (2002-09-23 17:49:00) [9]
тогда если не очень клопотно расскажите пожалуйта на том с чем знакомы ?? хотя бы в общих чертах ... или где это можна найти ??

// извините за настойчивость..

Termik (2002-09-23 17:32:00) [10]
Прошу прощения, с PostgreSQL не знаком , ничем не могу помочь ...

aGo (2002-09-23 17:29:00) [11]
пользую PostgreSQL и Delphi

Termik (2002-09-23 17:25:00) [12]
Чем пользуешься ?

aGo (2002-09-23 17:19:00) [13]
а поподробнее немножко можна ??
или где можна найти более детальную информацию ?

Termik (2002-09-23 17:14:00) [14]
Путем формирования динамического SQL с указанием имен полей.

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »