Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
IgorCCS
Member

Откуда:
Сообщений: 228
Есть ли аналог аггрегатной функции LIST в MS SQL 2005?
Которая возвращает список значений через разделитель.

Например если таблица Х:
F1
-----
1
2
3
4

то
select list(F1) from X
вернёт (1,2,3,4)
2 ноя 06, 13:57    [3347396]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
https://www.sql.ru/faq/faq_topic.aspx?fid=130
2 ноя 06, 13:58    [3347405]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
IgorCCS
Member

Откуда:
Сообщений: 228
Не подходит.
Если взять что группировать придётся максимум по N полям и ограничиться M типами данных то получится X вариантов функции GetDescription.

Х=1*M+2*M+3*M+4*M+...+(N-1)*M+N*M

Уменя вот в группировке ТОЛЬКО В 1 запросе 34 поля идёт.
Получается под каждый запрос свою функцию.
При каждом изменении запроса вероятность написания новой функции.
Ну и всё прочее счастье...

Если ошибаюсь поправьте. Сильно не бейте :)

Возможно ли на MS SQL 2005 написать полноценную пользовательскую аггрегатную функцию?

Сообщение было отредактировано: 2 ноя 06, 14:41
2 ноя 06, 14:39    [3347772]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Если взять что группировать придётся максимум по N полям и ограничиться M типами данных то получится X вариантов функции GetDescription
А что, передавать в функцию параметры, которые будут влиять на логику ее поведения, уже не модно ?
2 ноя 06, 14:40    [3347784]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Glory
Member

Откуда:
Сообщений: 104751
IgorCCS

Возможно ли на MS SQL 2005 написать полноценную пользовательскую аггрегатную функцию?

Открываете в хелпе раздел про CLR. Изучаете. И пишите свою агрегатку
2 ноя 06, 14:42    [3347791]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
IgorCCS
Не подходит.
...
Возможно ли на MS SQL 2005 написать полноценную пользовательскую аггрегатную функцию?
В MS SQL 2005 можно написать полноценную пользовательскую аггрегатную функцию на CLR. см в BOL: CLR User-Defined Aggregates
2 ноя 06, 14:44    [3347812]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
IgorCCS
Member

Откуда:
Сообщений: 228
За CLR большое спасибо. Разбираюсь.
Чтобы создать функцию CLR в SQL Server, необходимо выполнить следующие шаги:

1.Определить функцию как статический метод класса на языке, поддерживаемом .NET Framework. Дополнительные сведения о программировании функций в среде CLR см. в разделе CLR User-Defined Functions. После этого следует скомпилировать класс, чтобы создать сборку в .NET Framework, используя компилятор соответствующего языка.
2.Зарегистрировать сборку в SQL Server с помощью инструкции CREATE ASSEMBLY. Дополнительные сведения о сборках в SQL Server см. в разделе Сборки.
3.Создать функцию, ссылающуюся на зарегистрированную сборку, с помощью инструкции CREATE FUNCTION.

Сообщение было отредактировано: 2 ноя 06, 14:51
2 ноя 06, 14:51    [3347863]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
IgorCCS
За CLR большое спасибо. Разбираюсь.


IgorCCS

Чтобы создать функцию CLR в SQL Server, необходимо выполнить следующие шаги:
Спасибо, мы знаем :-)

Вопрос-то какой?
2 ноя 06, 15:13    [3348039]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
IgorCCS
Member

Откуда:
Сообщений: 228
А можно где нибудь взять эти сборки уже готовые?
Наверняка их уже куча написана.
2 ноя 06, 15:25    [3348155]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
IgorCCS
А можно где нибудь взять эти сборки уже готовые?
Наверняка их уже куча написана.
Наверняка, но я не видел. Можно на каких-нибуть забугорных форумах поискать.
2 ноя 06, 16:06    [3348498]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
alexeyvg
Наверняка их уже куча написана.
Вот, для строк прямо в БОЛ пример:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ru/denet9/html/5a188b50-7170-4069-acad-5de5c915f65d.htm

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private StringBuilder intermediateResult;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        this.intermediateResult = new StringBuilder();
    }

    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlString value)
    {
        if (value.IsNull)
        {
            return;
        }

        this.intermediateResult.Append(value.Value).Append(',');
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        this.intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (this.intermediateResult != null
            && this.intermediateResult.Length > 0)
        {
            output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
        }

        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}

CREATE ASSEMBLY MyAgg FROM 'C:\MyAgg.dll'
GO
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.Concatenate
2 ноя 06, 16:11    [3348540]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
IgorCCS
Member

Откуда:
Сообщений: 228
Это я видел.
Вот здесь именно про LIST говорится

здесь за деньги
[url=http://]http://www.sqlmag.com/Article/ArticleID/49983/sql_server_49983.html


вот только я 6 лет назад с MS VS имел дело :(
2 ноя 06, 16:55    [3348991]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
To IgorCCS:
Такой результат можно сделать и без функции:

declare @t table (F1 int)

insert @t
select 1 union all select 2 union all select 3 union all select 4

select replace((select F1 as 'data()' from @t for xml path('')),' ',',')
3 ноя 06, 14:11    [3353829]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Ну, и, плюс, сортировка в этом списке может быть любой:
select replace((select F1 as 'data()' from @t order by 1 desc for xml path('')),' ',',')
3 ноя 06, 14:14    [3353860]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
2Roman S. Golubin

гениально
3 ноя 06, 14:33    [3354043]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
alexeyvg
2Roman S. Golubin
гениально
Присоединяюсь.
29 мар 08, 10:57    [5476161]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
RomanT
Member

Откуда: Kharkov
Сообщений: 64
IgorCCS
Есть ли аналог аггрегатной функции LIST в MS SQL 2005?
Которая возвращает список значений через разделитель.

Например если таблица Х:
F1
-----
1
2
3
4

то
select list(F1) from X
вернёт (1,2,3,4)



--Представление значений поля таблицы в виде строки с разделителями
WITH t AS
(
SELECT 1 AS rn, 'aaa' AS f2
UNION ALL
SELECT 2, 'bbb'
UNION ALL
SELECT 3, 'ccc'
UNION ALL
SELECT 4, 'ddd'
),
t1 AS
(
SELECT rn,
       f2,
       1 AS level,
       CAST(f2 AS VARCHAR(MAX)) AS list_value
FROM t
WHERE rn = 1 
UNION ALL
SELECT t.rn AS rn,
       t.f2,
       t1.level + 1 AS level,
       t1.list_value + ',' + t.f2 AS list_value
FROM t1 INNER JOIN t ON t1.rn = t.rn - 1
)
SELECT list_value
FROM t1 WHERE rn = (SELECT MAX(rn) FROM t1)
OPTION (MAXRECURSION 0)

list_value
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aaa,bbb,ccc,ddd

(1 row(s) affected)
29 мар 08, 18:42    [5476663]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
dsfsdfsdfsdfs
Guest
.
14 ноя 08, 12:59    [6440606]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Andret
Member

Откуда: Киев
Сообщений: 84
А зачем такие сложности? Агрегатные там функции писать...

Старый дедовский способ действует во всех версиях, к тому же самый быстрый.
declare	@s varchar(8000)
select	@s = isNull(@s + ', ', '') + convert(varchar, id)
from	sysobjects
order by id desc
print @s
Есть, конечно нюансы с зависимостью плана от сортировки, но если это учитывать, то всё будет пучком!
14 ноя 08, 14:05    [6441230]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
Andret
Member

Откуда: Киев
Сообщений: 84
Andret
А зачем такие сложности? Агрегатные там функции писать...

Старый дедовский способ действует во всех версиях, к тому же самый быстрый.
declare	@s varchar(8000)
select	@s = isNull(@s + ', ', '') + convert(varchar, id)
from	sysobjects
order by id desc
print @s
Есть, конечно нюансы с зависимостью плана от сортировки, но если это учитывать, то всё будет пучком!


Кстати, если нужна группировка, то старый дедовский способ так-же действует. Правда удобнее это делать в update отсортированной таблицы.
14 ноя 08, 14:14    [6441319]     Ответить | Цитировать Сообщить модератору
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
Andret
А зачем такие сложности? Агрегатные там функции писать...

Старый дедовский способ действует во всех версиях, к тому же самый быстрый.
declare	@s varchar(8000)
select	@s = isNull(@s + ', ', '') + convert(varchar, id)
from	sysobjects
order by id desc
print @s
Есть, конечно нюансы с зависимостью плана от сортировки, но если это учитывать, то всё будет пучком!

точно, сам ещё вчера так писал. Тупанул из-за того чем сейчас занимаюсь.
14 ноя 08, 14:24    [6441443]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Есть ли аналог аггрегатной функции LIST в MS SQL 2005?  [new]
dbms_photoshop
Member

Откуда:
Сообщений: 5151
Гавриленко Сергей Алексеевич
//https://www.sql.ru/faq/faq_topic.aspx?fid=130
Времени, конечно, прошло уйма.
Но вариант для SQL 2000 еще с 2003 года известен как сомнительный: PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location.
Для SQL 7 тоже не внушает доверия. По крайней мере программист в некоторых случаях может ожидать упорядоченной конкатенации, а "внезапно" задействованный parallelism все испортит.
7 сен 11, 02:02    [11238228]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить