Форум: "Базы";
Текущий архив: 2002.10.14;
Скачать: [xml.tar.bz2];

Вниз

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

← →
aGo   (2002-09-23 17:10) [0]

Подскажите, пожалуйста, есть ли возможность с помощью SQL сформировать запрос из главной и подчиненной табличек, но таким образом, чтобы в результирующем наборе данных колличество полей зависело от колличества записей в подчиненной табличке ??

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

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

---

← →
Termik   (2002-09-23 17:14) [1]

Путем формирования динамического SQL с указанием имен полей.

---

← →
aGo   (2002-09-23 17:19) [2]

а поподробнее немножко можна ??
или где можна найти более детальную информацию ?

---

← →
Termik   (2002-09-23 17:25) [3]

Чем пользуешься ?

---

← →
aGo   (2002-09-23 17:29) [4]

пользую PostgreSQL и Delphi

---

← →
Termik   (2002-09-23 17:32) [5]

Прошу прощения, с PostgreSQL не знаком , ничем не могу помочь ...

---

← →
aGo   (2002-09-23 17:49) [6]

тогда если не очень клопотно расскажите пожалуйта на том с чем знакомы ?? хотя бы в общих чертах ... или где это можна найти ??

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

---

← →
Delirium   (2002-09-23 18:08) [7]

Рабочий из программы в качестве примера 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

---

← →
BlackTiger   (2002-09-23 18:22) [8]

Насколько я понял - это простой "crosstab".
У меня есть текст хранимой процедуры для MSSQL (взято с SQLTeam.com, кое-что изменено), которая на выходе выдает "перевертыш".
Кинуть?

---

← →
Delirium   (2002-09-23 18:25) [9]

Да, похоже - но есть отличия, например - порядок колонок iParamPos т.е. колонки формируясь динамически выстраиваются в порядке, не выводящемся в результате

---

← →
aGo   (2002-09-23 18:34) [10]

Огромное Вам спасибо!
Сейчас буду разбираться !

---

← →
aGo   (2002-09-23 19:03) [11]

>BlackTiger

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

---

← →
BlackTiger   (2002-09-23 21:13) [12]

Вот линк на статейку " 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"

---

← →
BlackTiger   (2002-09-23 21:19) [13]

Моя доработанная версия (может кому пригодится, принимаю предложения по оптимизации, хотя и так довольно быстро работает) - длина конечного запроса до 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
---------------------------------------------------

---

← →
kaif   (2002-09-23 23:18) [14]

А почему бы не подумать о том, чтобы денормализацию на клиенте делать? Мне кажется, что утяжеление SQL не всегда оправдано.
Если клиент написан на Delphi, то можно при помощи компонента XLReport от Afalina Soft (или руками, если этот компонент не нравится) выбросить такой отчет в Excel.
Это так, в виде идеи...

---

Страницы: 1 вся ветка

Форум: "Базы";
Текущий архив: 2002.10.14;
Скачать: [xml.tar.bz2];

Наверх

Память: 0.5 MB
Время: 0.008 c