Microsoft SQL Server
Transact-SQL

Сложение символьных полей в запросе

Опубликовано: 12 окт 02
Рейтинг:

Автор: Jimmers+VVG
Прислал: Glory

Q.
Есть таблица:

Тип	Имя	Количество
1	Молоко	5 
1	Сметана	6 
2	Гвозди	44 
3	Машины	1 


Необходимо получить на выходе (2 поля)
Тип	Описание 
1	"Молоко: 5, Сметана: 6" 
2	"Гвозди: 44" 
3	"Машины: 1"



A.

Для решение такой задачи нужно чуть модифицировать метод, описанный в топике ФАКа "Нарастающий итог"

Для SQL 2000
USE tempdb
GO
CREATE TABLE dbo.TEST([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
GO

INSERT dbo.TEST VALUES(1, N'Молоко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвозди', 44)
INSERT dbo.TEST VALUES(3, N'Машины', 1)
GO

CREATE FUNCTION dbo.GetDescription(@id INTEGER)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @Result NVARCHAR(4000)
SET @Result = ''
SELECT @Result = @Result + [Имя] + ': ' + CAST([Количество] AS NVARCHAR(10)) + ' ' FROM dbo.TEST WHERE [Тип] = @id
RETURN '"' + RTRIM(@Result) + '"'
END
GO

SELECT DISTINCT [Тип], dbo.GetDescription([Тип]) FROM dbo.TEST
GO


Для SQL 7
CREATE TABLE dbo.TEST([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
GO


INSERT dbo.TEST VALUES(1, N'Молоко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвозди', 44)
INSERT dbo.TEST VALUES(3, N'Машины', 1)
GO


create table results (id int,name varchar(200),res varchar(7800))

create unique clustered index ix_results on results (id,name)

insert into results select [Тип],[Имя]+': '+cast([Количество] as varchar(10)),'' from dbo.TEST
order by 1,2

declare @Res varchar(8000),@ID int
set @Res = '' set @ID=0

update results set @Res=res=case when @ID=ID then Name+', '+@Res else Name end, @ID=ID

select id,max(res) from results group by id

drop table results
drop table dbo.TEST


!NB Данный способ может выдавать разные результаты при использовании ORDER BY. Подробности http://support.microsoft.com/kb/287515/en-us

Для SQL2005
use tempdb
go
drop table test
go
create table dbo.TEST ([Тип] INTEGER, [Имя] NVARCHAR(100), [Количество] INTEGER)
go
INSERT dbo.TEST VALUES(1, N'Молоко', 5)
INSERT dbo.TEST VALUES(1, N'Сметана', 6)
INSERT dbo.TEST VALUES(2, N'Гвозди', 44)
INSERT dbo.TEST VALUES(3, N'Машины', 1)

--Вариант 1
select [Тип] ,( select [Имя]+': '+cast([Количество] as nvarchar)+ ',' as 'data()' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
from dbo.TEST t1
group by [Тип]

--Вариант 2
;with
t1 as 
	(select [Тип], cast([Имя] as nvarchar)+': '+cast([Количество] as nvarchar) as [Имя], row_number() over(partition by [Тип] order by [Тип]) rn
	from dbo.TEST
	),
tr([Тип], [Имя],lev) as
	(select t1.[Тип], cast(t1.[Имя] as nvarchar) , 2 from t1 where t1.rn = 1
	union all
	select t1.[Тип], cast(tr.[Имя] +','+t1.[Имя] as nvarchar), lev + 1
	from t1 join tr on (t1.[Тип] = tr.[Тип] and t1.rn = tr.lev
	)
)
select [Тип], max([Имя]) names from tr group by [Тип]

Комментарии


  • Спасибо огромное!

  • Выручили. В который раз.

  • хм
    для очень больших таблиц (140 тыс например)
    дохрена времени

  • >> Используя reverse и SUBSTRING можно извратица и без второго запроса "откусить" последний знак. ))

    А если кусок: ...select [Имя]+ ',' from...
    Заменить на: ...select ',' + [Имя] from...
    То тогда понадобиться откусывать не последнюю, а первую запятую. В этом случае можно сразу использовать substring(), без двойного вызова reverse()

  • "06 января 2014, 10:57 michael R
    --Вариант 1
    select [Тип] ,( select [Имя]+ ',' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
    from dbo.TEST t1
    group by [Тип]

    а как анулировать последнюю запятую для каждой строчки ?
    без второго запроса ?"
    Я так же столкнулся с таким вопросом. И подумав, решил что можно по пробовать как то так :

    select [Тип] ,reverse(SUBSTRING(reverse( select [Имя]+ ',' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') ),2,100500))
    from dbo.TEST t1
    group by [Тип]

    Используя reverse и SUBSTRING можно извратица и без второго запроса "откусить" последний знак. ))

  • --Вариант 1
    select [Тип] ,( select [Имя]+ ',' from test t2 where t1.[Тип]=t2.[Тип] for xml path('') )
    from dbo.TEST t1
    group by [Тип]

    а как анулировать последнюю запятую для каждой строчки ?
    без второго запроса ?

  • 17 мая 2012, 16:50 Гавриленко Сергей Алексеевич

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

  • Это просто гениально!!! Спасибо огромное!

  • 05 апреля 2011, 23:23 Двоичник

    ищите CLR C# fn_concatenate

  • а ведь этот метод в sqlt 200-ном всеж нагнулся, один скуль из 20 сгенерил таки иной план исполнения, сначала commpute sclar, потом sort и все, сумма обломалась, я так и знала

  • Есть еще несколько способов сделать сроковую агрегацию (через XML, например), я недавно написал об этом статью вот здесь: http://d2nx.ru/string-aggregation . За идею с CTE спасибо - добавлю себе для полноты.

  • Тяжело для понимание, потому что поля на русском

  • Спасибо.

  • Все бы хорошо работало, только у меня строка c результатом nvarchar(4000) обрезается (truncate), т.е. не хватает 4000.
    Что делать?

    Спасибо

  • http://msdn.microsoft.com/ru-ru/library/ms131056.aspx

  • Тоже пришлось недавно столкнутся с подобной задачей, времени было мало, наскору руку предлагаю два варианта

    -- Задача: Необходимо получить список счетов, через запятую, для каждого корр-та
    -- Исходная таблица
    IF object_id('tempdb..##t_Acc') is not null DROP TABLE ##t_Acc
    CREATE TABLE ##t_Acc (CACCOUNT varchar(20), CORR int)
    INSERT ##t_Acc VALUES('101', 1)
    INSERT ##t_Acc VALUES('102', 1)
    INSERT ##t_Acc VALUES('201', 2)
    INSERT ##t_Acc VALUES('202', 2)
    INSERT ##t_Acc VALUES('203', 2)
    INSERT ##t_Acc VALUES('204', 2)
    INSERT ##t_Acc VALUES('205', 2)
    INSERT ##t_Acc VALUES('301', 3)
    INSERT ##t_Acc VALUES('302', 3)
    INSERT ##t_Acc VALUES('303', 3)
    select * from ##t_Acc

    -- Решение 1: (через курсор)
    CREATE TABLE ##ACC_LINK (CORR int, CACC_LIST varchar(1000))

    DECLARE @iCorr int
    DECLARE @cAcc varchar(20)
    DECLARE @cACC_LIST varchar(1000)
    DECLARE c_LIST_CORR CURSOR FOR
    SELECT CORR FROM ##t_Acc GROUP BY CORR

    OPEN c_LIST_CORR
    FETCH NEXT FROM c_LIST_CORR INTO @iCorr

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @cACC_LIST = ''
    DECLARE c_LIST_ACC CURSOR FOR
    SELECT CACCOUNT FROM ##t_Acc WHERE CORR = @iCorr
    OPEN c_LIST_ACC
    FETCH NEXT FROM c_LIST_ACC INTO @cAcc

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @cACC_LIST = @cACC_LIST + @cAcc
    FETCH NEXT FROM c_LIST_ACC INTO @cAcc
    IF @@FETCH_STATUS = 0
    SELECT @cACC_LIST = @cACC_LIST + ', '
    END
    CLOSE c_LIST_ACC
    DEALLOCATE c_LIST_ACC
    INSERT ##ACC_LINK
    SELECT @iCorr AS CORR
    , @cACC_LIST AS CACC_LIST
    FETCH NEXT FROM c_LIST_CORR INTO @iCorr
    END
    CLOSE c_LIST_CORR
    DEALLOCATE c_LIST_CORR

    select * from ##ACC_LINK
    IF object_id('tempdb..##ACC_LINK') is not null DROP TABLE ##ACC_LINK

    -- Решение 2: (последовательное сложение строк, !!! здесь важно чтобы таблица была отсортирована по CORR)
    DECLARE @cBuf varchar(1000)
    , @cc int

    SELECT CACCOUNT
    , CORR
    , cast('' as varchar(1000)) as acc_list
    INTO #tmptab
    FROM ##t_Acc

    SET @cBuf = ''
    SET @cc = (select top 1 CORR from #tmptab)

    UPDATE #tmptab
    SET @cBuf = acc_list = left(case
    when @cc = CORR
    then @cBuf + CACCOUNT + ', '
    else CACCOUNT + ', '
    end, 1000)
    , @cc = CORR

    UPDATE #tmptab
    SET acc_list = SUBSTRING(acc_list,0,LEN(acc_list))

    SELECT CORR
    , max(acc_list) as [CACCOUNT_LIST]
    FROM #tmptab
    GROUP BY CORR

    -- удаляем временные таблицы из tempdb
    IF object_id('tempdb..#tmptab') is not null DROP TABLE #tmptab
    IF object_id('tempdb..##t_Acc') is not null DROP TABLE ##t_Acc

  • Вариант для 2005:
    create table test (id int, name varchar(20))
    go
    insert test values (1,'aaa')
    insert test values (1,'bbb')
    insert test values (1,'ccc')
    insert test values (2,'ddd')
    insert test values (3,'eee')


    with
    t1 as (select id, name
    row_number()over(partition by id order by id) rn
    from test),

    tr(id, name, lev) as
    (select t1.id, cast(t1.name as varchar), 2 from t1 where t1.rn = 1
    union all
    select t1.id, cast(tr.name+','+t1.name as varchar), lev + 1
    from t1 join tr on (t1.id = tr.id and t1.rn = tr.lev)
    )
    select id, max(name) names from tr group by id

  • вариант с xml поражает своей элегантностью и непонятностью, надо почитать что-нить на эту тему

  • вариант с xml поражает своей элегантностью и непонятностью, надо почитать что-нить на эту тему

  • вариант с xml поражает своей элегантностью и непонятностью, надо почитать что-нить на эту тему

  • одна из саамых посещаемых мною страниц)))

    сюда мне проще добратся чем записать куданить ф сваи заметки

  • +5
    а юзание функции быстрее будет чем в теле курсора?

  • Блин, куда деваются старые обсуждения, Там люди к чему-то присоединяются, а ты как лох и не знаешь к чему :(

  • А можно ли в SQL 2005 написать агрегирующую фунцкию (как в оракле)?

    Т.е. чтобы
    create table t (a varchar(1))
    insert into t values ('a')
    insert into t values ('b')
    insert into t values ('c')

    select aggr_concat(a) aggr_a from t
    выдавал бы:
    aggr_a
    ----------
    abc

  • А можно ли в SQL 2005 написать агрегирующую фунцкию (как в оракле)?

    Т.е. чтобы
    create table t (a varchar(1))
    insert into t values ('a')
    insert into t values ('b')
    insert into t values ('c')

    select aggr_concat(a) aggr_a from t
    выдавал бы:
    aggr_a
    ----------
    abc

  • А как нибудь это можно реализовать без использования дополнительных переменных?

  • а как такое же сделать для SQl2000
    если используется переменная типа таблица ?
    не выходит у меня

  • Мне подошел вариант loki1984. с добавлением group by в ( select txt + ' ;' as 'data()' from test t2 where t1.id=t2.id for xml path('') )
    Огромное спасибо !

  • Была такая же задача, решил, а потом тут такое же решение нашол :)

  • Спасибо, отличный метод!

  • 21 февраля 2007, 18:22 Лиман Артём

    Для SQL 2000 варианта не добавляеца запятая, как описано в постановке задачи.

  • Есть другой способ:

    drop table test
    go
    create table test (id int, txt varchar(20))
    go
    insert test values (1,'привет')
    insert test values (1,'дорогой')
    insert test values (1,'друг')
    insert test values (2,'test')


    select id ,( select txt + ' ;' as 'data()' from test t2 where t1.id=t2.id for xml path('') )
    from test t1
    group by id

  • SELECT @local_variable is usually used to return a single value into the variable. It can return multiple values if, for example, expression is the name of a column. If the SELECT statement returns more than one value, the variable is assigned the last value returned.

    Это по то что написано в документации про такой селект.

  • Странно что это вообще работает, но работает однако. ИМХО, недокументированные возможности используются, это не есть гуд, т.к. работать может перестать на следующих версиях.

  • 20 июня 2006, 17:39 Запросник

    Блин, ахренеть

  • Супер... Делал с курсорами)) но не терял надежды

  • Красивый метод :) Спасибо.

  • Опупеть как рульно! :)

  • 14 ноября 2005, 09:32 Начинающий программист.

    5 балов!

  • Вот ведь как, а я лупень курсоры гонял :)

  • А почему так?!?!

  • Otсоединяюсь к Владимиру

  • Присоединяюсь к Владимиру

  • Присоединяюсь к Владимиру



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Сложение символьных полей в запросе