Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как реализовать аналог SUM по varchar-у  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
Всем добрового времени суток.

К примеру есть табличка:
declare @tmp table (i int, j varchar(100))

insert into @tmp (i, j) values (1, 'P1')
insert into @tmp (i, j) values (1, 'P2')
insert into @tmp (i, j) values (1, 'P3')
insert into @tmp (i, j) values (2, 'P4')
insert into @tmp (i, j) values (2, 'P5')
insert into @tmp (i, j) values (3, 'P6')
insert into @tmp (i, j) values (4, null)

Задача заключается в том, чтобы получить из такой таблички результат вида:

i concatenation
-----------------
1 P1, P2, P3
2 P4, P5
3 P6
4 null

Т.е. руки так и чешутся написать что-нибудь в духе:

select
   i,
   ConcatenateStr(j, ',') --некая агрегирующая функция
from
   @tmp
group by
   i

... ан мечты.., да и UDF свою написать нельзя, т.к. на машине, где этот запрос будет отрабатывать, банально не будет прав на создание своей функции.

Отсюда вопрос, если возможность решить эту задачу с помощью одного select-запроса, не прибегая к всяким извратам (промежуточные временные таблички и т.п.)?
6 июл 09, 23:32    [7383619]     Ответить | Цитировать Сообщить модератору
 Как начальная идея. Гугл может и полное решение дать.  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 575
create table t1(line# int not null, word varchar(20))
GO
insert into t1 values (1,'A')
insert into t1 values (1,'B')
insert into t1 values (1,'C')
insert into t1 values (1,'D')
GO
select * from t1
GO
WITH ABC (line#, word) AS
(
    SELECT 1, CAST('' AS VARCHAR(8000)) 
    UNION ALL
    SELECT B.line# + 1, B.word +  A.word + ', ' 
    FROM 
( SELECT Row_Number() OVER (ORDER BY line#) AS RN, word FROM T1 ) A 
    INNER JOIN ABC B ON A.RN = B.line# 
)
SELECT TOP 1 word FROM ABC ORDER BY line# DESC
GO
7 июл 09, 00:38    [7383709]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
мимошел
Guest
-=FlinT=-,

задача с sql-ex.ru ?
7 июл 09, 01:02    [7383739]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
мимошел
автор
задача с sql-ex.ru?


Нет, вполне себе такая реальная задача, на live-базе.

Sergei.Agalakov
Спасибо большое за начальную идею, можно будет и её поковырять, хотя как-то меня сомнения терзают насчет использования такого рекурсивного запроса на результирующей табличке в ~4млн строк...
7 июл 09, 10:58    [7384724]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
Troglodit
Member

Откуда:
Сообщений: 499
если в MSSQL 2005 повилась комада типа INSERT_or_UPDATE, то создаете временную таблицу #tmp_1
inser_or_update #tmp_1 from select ...
on insert #tmp
on update update #tmp set j=j+#tmp_j
where #tmp_1.i=#tmp.i
не уверен что это все будет работать на 4 -х мил. записей.
7 июл 09, 16:58    [7387746]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
declare @tmp table (i int, j varchar(100))

insert into @tmp (i, j) values (1, 'P1')
insert into @tmp (i, j) values (1, 'P2')
insert into @tmp (i, j) values (1, 'P3')
insert into @tmp (i, j) values (2, 'P4')
insert into @tmp (i, j) values (2, 'P5')
insert into @tmp (i, j) values (3, 'P6')
insert into @tmp (i, j) values (4, null)

SELECT
 T.i,
 STUFF((SELECT ','+TT.j FROM @tmp TT WHERE TT.i=T.i ORDER BY TT.j FOR XML PATH('')),1,1,'')j
FROM @tmp T
GROUP BY T.i
ORDER BY T.i;
7 июл 09, 17:11    [7387855]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
iap, спасибо, вот это уже более интересно. Надо будет на досуге почитать про "FOR XML"...
7 июл 09, 17:35    [7388063]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
Andret
Member

Откуда: Киев
Сообщений: 84
А можно и старым дедовским:

declare @tmp table (i int, j varchar(100), id int identity primary key, [str] varchar(255))
insert @tmp (i, j) values (1, 'P1')
insert @tmp (i, j) values (1, 'P2')
insert @tmp (i, j) values (1, 'P3')
insert @tmp (i, j) values (2, 'P4')
insert @tmp (i, j) values (2, 'P5')
insert @tmp (i, j) values (3, 'P6')
insert @tmp (i, j) values (4, null)

declare	@new bit,
	@i int,
	@str varchar(255)

update	@tmp
set	@new	= case when @i is null or i <> @i then 1 else 0 end,
	@i	= i,
	@str	= case @new when 0 then isNull(@str + ', ', '') else '' end + j,
	[str]	= @str

select	a.i, a.[str]
from	@tmp a
join	(
	select	i, max(id) as id
	from	@tmp
	group by i
	) b on a.id = b.id

А 4 млн записей это совсем не много :-).
7 июл 09, 18:46    [7388441]     Ответить | Цитировать Сообщить модератору
 Re: Как реализовать аналог SUM по varchar-у  [new]
-=FlinT=-
Member

Откуда:
Сообщений: 91
Andret спасибо, запросик крышесносящий, я даже чуть не закурил, хотя вообще не курю :-)
7 июл 09, 19:38    [7388572]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить