Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Значения чере запятую  [new]
Rankatan
Member

Откуда:
Сообщений: 250
env
Rankatan,

Как ты себе представляешь решение через unpivot со всеми прежними ограничениями?

unpivot подойдет если известно максимальное количество элементов для склейки. Но раз количество строк заранее не известно, то вариант unpivot не подходит.
7 авг 17, 16:51    [20706034]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 5964
Rankatan,

в pivot ещё поверю, а вот с unpivot прошу показать
7 авг 17, 16:54    [20706051]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Rankatan
Member

Откуда:
Сообщений: 250
env
Rankatan,

в pivot ещё поверю, а вот с unpivot прошу показать

Да PIVOT, перепутал
7 авг 17, 16:57    [20706060]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
ADx
Guest
assmsk,
придумывайте, рассказывайте. Бредовое задание.
7 авг 17, 17:11    [20706108]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Интересно чем мотивирует препод такую постановку задачи?
Ну реально же неудобно шубу в трусы заправлять.
7 авг 17, 19:14    [20706489]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
Держи!

Используем то обстоятельство, что после группировки, учитывающей возможные повторы id, на один name будет приходиться не более 2^32 строк - уникальных интов. Если известен предел кол-ва строк на один name, то количество подзапросов можно уменьшить.

+ простыня кода

DECLARE @t TABLE ( name varchar(1), id int)

Insert into @t(name,id) values ('a',1)
Insert into @t(name,id) values ('a',2)
Insert into @t(name,id) values ('a',2) -- добавим повтор
Insert into @t(name,id) values ('a',3)
Insert into @t(name,id) values ('a',10)
Insert into @t(name,id) values ('a',22)
Insert into @t(name,id) values ('a',24)
Insert into @t(name,id) values ('b',1)
Insert into @t(name,id) values ('b',4)

SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt/*, rn, d*2 AS d*/ FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT ss.name, CAST(LEFT(ss.txt, LEN(ss.txt) - 1) AS VARCHAR(MAX)) AS txt, ROW_NUMBER() OVER (PARTITION BY name ORDER BY txt) - 1 AS rn, CAST(2 AS BIGINT) AS d
FROM (
	-- После такой группировки на один name будет не больше 2^32 строк
	SELECT name, REPLICATE(CAST(id AS VARCHAR(20)) + ',', COUNT(*)) AS txt FROM @t GROUP BY name, id
) ss
) x01
) x02 WHERE RN % (d / 2) = 0
) x03 WHERE RN % (d / 2) = 0
) x04 WHERE RN % (d / 2) = 0
) x05 WHERE RN % (d / 2) = 0
) x06 WHERE RN % (d / 2) = 0
) x07 WHERE RN % (d / 2) = 0
) x08 WHERE RN % (d / 2) = 0
) x09 WHERE RN % (d / 2) = 0
) x10 WHERE RN % (d / 2) = 0
) x11 WHERE RN % (d / 2) = 0
) x12 WHERE RN % (d / 2) = 0
) x13 WHERE RN % (d / 2) = 0
) x14 WHERE RN % (d / 2) = 0
) x15 WHERE RN % (d / 2) = 0
) x16 WHERE RN % (d / 2) = 0
) x17 WHERE RN % (d / 2) = 0
) x18 WHERE RN % (d / 2) = 0
) x19 WHERE RN % (d / 2) = 0
) x20 WHERE RN % (d / 2) = 0
) x21 WHERE RN % (d / 2) = 0
) x22 WHERE RN % (d / 2) = 0
) x23 WHERE RN % (d / 2) = 0
) x24 WHERE RN % (d / 2) = 0
) x25 WHERE RN % (d / 2) = 0
) x26 WHERE RN % (d / 2) = 0
) x27 WHERE RN % (d / 2) = 0
) x28 WHERE RN % (d / 2) = 0
) x29 WHERE RN % (d / 2) = 0
) x30 WHERE RN % (d / 2) = 0
) x31 WHERE RN % (d / 2) = 0

7 авг 17, 22:11    [20706752]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
БЕЗ ИСПОЛЬЗОВАНИЯ:
1) циклов
2) переменных
3) рекурсии
4)xml path
функция с
select @a = @a + ',' + FieldXXX ........

Часто приходится использовать. Работает на любой версии и вполне быстро.
8 авг 17, 10:14    [20707336]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
LSV
БЕЗ ИСПОЛЬЗОВАНИЯ:
1) циклов
2) переменных
3) рекурсии
4)xml path
функция с
select @a = @a + ',' + FieldXXX ........

Часто приходится использовать. Работает на любой версии и вполне быстро.


Не подойдет по условиям. Нельзя использовать переменные, а также надо получить не одну строку, а таблицу со списком Id для каждого Name.
8 авг 17, 10:37    [20707406]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
Gluck_13
Держи!

Используем то обстоятельство, что после группировки, учитывающей возможные повторы id, на один name будет приходиться не более 2^32 строк - уникальных интов. Если известен предел кол-ва строк на один name, то количество подзапросов можно уменьшить.

+ простыня кода

DECLARE @t TABLE ( name varchar(1), id int)

Insert into @t(name,id) values ('a',1)
Insert into @t(name,id) values ('a',2)
Insert into @t(name,id) values ('a',2) -- добавим повтор
Insert into @t(name,id) values ('a',3)
Insert into @t(name,id) values ('a',10)
Insert into @t(name,id) values ('a',22)
Insert into @t(name,id) values ('a',24)
Insert into @t(name,id) values ('b',1)
Insert into @t(name,id) values ('b',4)

SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt/*, rn, d*2 AS d*/ FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT name, CASE WHEN (COUNT(*) OVER (PARTITION BY name, rn / d) = 1) THEN txt ELSE MIN(txt) OVER (PARTITION BY name, rn / d) + ',' + MAX(txt) OVER (PARTITION BY name, rn / d) END AS txt, rn, d*2 AS d FROM (
SELECT ss.name, CAST(LEFT(ss.txt, LEN(ss.txt) - 1) AS VARCHAR(MAX)) AS txt, ROW_NUMBER() OVER (PARTITION BY name ORDER BY txt) - 1 AS rn, CAST(2 AS BIGINT) AS d
FROM (
	-- После такой группировки на один name будет не больше 2^32 строк
	SELECT name, REPLICATE(CAST(id AS VARCHAR(20)) + ',', COUNT(*)) AS txt FROM @t GROUP BY name, id
) ss
) x01
) x02 WHERE RN % (d / 2) = 0
) x03 WHERE RN % (d / 2) = 0
) x04 WHERE RN % (d / 2) = 0
) x05 WHERE RN % (d / 2) = 0
) x06 WHERE RN % (d / 2) = 0
) x07 WHERE RN % (d / 2) = 0
) x08 WHERE RN % (d / 2) = 0
) x09 WHERE RN % (d / 2) = 0
) x10 WHERE RN % (d / 2) = 0
) x11 WHERE RN % (d / 2) = 0
) x12 WHERE RN % (d / 2) = 0
) x13 WHERE RN % (d / 2) = 0
) x14 WHERE RN % (d / 2) = 0
) x15 WHERE RN % (d / 2) = 0
) x16 WHERE RN % (d / 2) = 0
) x17 WHERE RN % (d / 2) = 0
) x18 WHERE RN % (d / 2) = 0
) x19 WHERE RN % (d / 2) = 0
) x20 WHERE RN % (d / 2) = 0
) x21 WHERE RN % (d / 2) = 0
) x22 WHERE RN % (d / 2) = 0
) x23 WHERE RN % (d / 2) = 0
) x24 WHERE RN % (d / 2) = 0
) x25 WHERE RN % (d / 2) = 0
) x26 WHERE RN % (d / 2) = 0
) x27 WHERE RN % (d / 2) = 0
) x28 WHERE RN % (d / 2) = 0
) x29 WHERE RN % (d / 2) = 0
) x30 WHERE RN % (d / 2) = 0
) x31 WHERE RN % (d / 2) = 0



Твою ж мать, экспресс не тянет:
Msg 8621, Level 17, State 1, Line 13
The query processor ran out of stack space during query optimization. Please simplify the query.
8 авг 17, 11:21    [20707648]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
Шыфл
Твою ж мать, экспресс не тянет:
Msg 8621, Level 17, State 1, Line 13
The query processor ran out of stack space during query optimization. Please simplify the query.


Максимум 30 повторов
8 авг 17, 11:31    [20707685]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Кесарь
Member

Откуда:
Сообщений: 453
Remind
Интересно чем мотивирует препод такую постановку задачи?
Ну реально же неудобно шубу в трусы заправлять.


Да чем, чем? Классическим: "чтоб служба мёдом не казалась". Вузовский вариант армейского бытия.
8 авг 17, 11:52    [20707787]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
iiyama
Member

Откуда:
Сообщений: 642
assmsk,
кстати а Left join на себя кол-вом превышающим заранее неизвестное кол-во строк, подходит в качестве решения ?
8 авг 17, 12:09    [20707887]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
[quot Шыфл]
Gluck_13
Держи!

Используем то обстоятельство, что после группировки, учитывающей возможные повторы id, на один name будет приходиться не более 2^32 строк - уникальных интов. Если известен предел кол-ва строк на один name, то количество подзапросов можно уменьшить.

Твою ж мать, экспресс не тянет:
Msg 8621, Level 17, State 1, Line 13
The query processor ran out of stack space during query optimization. Please simplify the query.


Проверял на Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

Запустил на 2008R2 - действительно, 30 max

В принципе, 30-ти подзапросов должно хватить (даже с лихвой), ибо в один VARCHAR(MAX) можно запихнуть не более 2 GB, куда весь числовой ряд интов (с учетом минусов и запятых) тупо не влезет.
8 авг 17, 12:53    [20708074]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
[quot Gluck_13]
Шыфл
пропущено...


Проверял на Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

Запустил на 2008R2 - действительно, 30 max

В принципе, 30-ти подзапросов должно хватить (даже с лихвой), ибо в один VARCHAR(MAX) можно запихнуть не более 2 GB, куда весь числовой ряд интов (с учетом минусов и запятых) тупо не влезет.


Вдогонку - если в запросе указать
....
) x31 WHERE RN % (d / 2) = 0
OPTION (FORCE ORDER)


, то работает
8 авг 17, 12:57    [20708104]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Кесарь
Member

Откуда:
Сообщений: 453
iiyama
assmsk,
кстати а Left join на себя кол-вом превышающим заранее неизвестное кол-во строк, подходит в качестве решения ?


Коллега, что-то не въехал в суть. Не продемонстрируете в виде кода?
8 авг 17, 13:09    [20708189]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
iiyama
Member

Откуда:
Сообщений: 642
Кесарь,
Людям со слабым сердцем не открывать
+ генерилку select-а напишите сами, тк она выходит за рамки изначальных условий :)


+

create table #t0(id int identity(1,1), lval nvarchar(50), pval int)
create table #t(N int, lval nvarchar(50), pval varchar(3), primary key(N, lval))


insert into #t0
select t1.name, ABS(CHECKSUM(NEWID())) % 100 
from
(
select name from master..spt_values where type='A'
)t1,
(
select name from master..spt_values where type='A'
)t2,
(
select name from master..spt_values where type='A'
)t3

insert into #t 
select row_number()over(partition by lval order by id), lval, pval as varchar(3)) from #t0



select t1.lval, 
ISNULL(t1.pval,'') 
+ISNULL(','+t2.pval,'')
+ISNULL(','+t3.pval,'')
+ISNULL(','+t4.pval,'')
+ISNULL(','+t5.pval,'')
+ISNULL(','+t6.pval,'')
+ISNULL(','+t7.pval,'')
+ISNULL(','+t8.pval,'')
+ISNULL(','+t9.pval,'')
+ISNULL(','+t10.pval,'')
+ISNULL(','+t11.pval,'')
+ISNULL(','+t12.pval,'')
+ISNULL(','+t13.pval,'')
+ISNULL(','+t14.pval,'')
+ISNULL(','+t15.pval,'')
+ISNULL(','+t16.pval,'')
+ISNULL(','+t17.pval,'')
+ISNULL(','+t18.pval,'')
+ISNULL(','+t19.pval,'')
+ISNULL(','+t20.pval,'')
+ISNULL(','+t21.pval,'')
+ISNULL(','+t22.pval,'')
+ISNULL(','+t23.pval,'')
+ISNULL(','+t24.pval,'')
+ISNULL(','+t25.pval,'')
+ISNULL(','+t26.pval,'')
+ISNULL(','+t27.pval,'')
+ISNULL(','+t28.pval,'')
+ISNULL(','+t29.pval,'')
+ISNULL(','+t30.pval,'')
+ISNULL(','+t31.pval,'')
+ISNULL(','+t32.pval,'')
+ISNULL(','+t33.pval,'')
+ISNULL(','+t34.pval,'')
+ISNULL(','+t35.pval,'')
+ISNULL(','+t36.pval,'')
+ISNULL(','+t37.pval,'')
+ISNULL(','+t38.pval,'')
+ISNULL(','+t39.pval,'')
+ISNULL(','+t40.pval,'')
+ISNULL(','+t41.pval,'')
+ISNULL(','+t42.pval,'')
+ISNULL(','+t43.pval,'')
+ISNULL(','+t44.pval,'')
+ISNULL(','+t45.pval,'')
+ISNULL(','+t46.pval,'')
+ISNULL(','+t47.pval,'')
+ISNULL(','+t48.pval,'')
+ISNULL(','+t49.pval,'')
+ISNULL(','+t50.pval,'')
+ISNULL(','+t51.pval,'')
+ISNULL(','+t52.pval,'')
+ISNULL(','+t53.pval,'')
+ISNULL(','+t54.pval,'')
+ISNULL(','+t55.pval,'')
+ISNULL(','+t56.pval,'')
+ISNULL(','+t57.pval,'')
+ISNULL(','+t58.pval,'')
+ISNULL(','+t59.pval,'')
+ISNULL(','+t60.pval,'')
+ISNULL(','+t61.pval,'')
+ISNULL(','+t62.pval,'')
+ISNULL(','+t63.pval,'')
+ISNULL(','+t64.pval,'')
+ISNULL(','+t65.pval,'')
+ISNULL(','+t66.pval,'')
+ISNULL(','+t67.pval,'')
+ISNULL(','+t68.pval,'')
+ISNULL(','+t69.pval,'')
+ISNULL(','+t70.pval,'')
+ISNULL(','+t71.pval,'')
+ISNULL(','+t72.pval,'')
+ISNULL(','+t73.pval,'')
+ISNULL(','+t74.pval,'')
+ISNULL(','+t75.pval,'')
+ISNULL(','+t76.pval,'')
+ISNULL(','+t77.pval,'')
+ISNULL(','+t78.pval,'')
+ISNULL(','+t79.pval,'')
+ISNULL(','+t80.pval,'')
+ISNULL(','+t81.pval,'')
+ISNULL(','+t82.pval,'')
+ISNULL(','+t83.pval,'')
+ISNULL(','+t84.pval,'')
+ISNULL(','+t85.pval,'')
+ISNULL(','+t86.pval,'')
+ISNULL(','+t87.pval,'')
+ISNULL(','+t88.pval,'')
+ISNULL(','+t89.pval,'')
+ISNULL(','+t90.pval,'')
+ISNULL(','+t91.pval,'')
+ISNULL(','+t92.pval,'')
+ISNULL(','+t93.pval,'')
+ISNULL(','+t94.pval,'')
+ISNULL(','+t95.pval,'')
+ISNULL(','+t96.pval,'')
+ISNULL(','+t97.pval,'')
+ISNULL(','+t98.pval,'')
+ISNULL(','+t99.pval,'')
+ISNULL(','+t100.pval,'')
+ISNULL(','+t101.pval,'')
from #T t1
LEFT JOIN #T t2 ON t1.lval=t2.lval AND t1.N+1=t2.N  
LEFT JOIN #T t3 ON t2.lval=t3.lval AND t2.N+1=t3.N  
LEFT JOIN #T t4 ON t3.lval=t4.lval AND t3.N+1=t4.N  
LEFT JOIN #T t5 ON t4.lval=t5.lval AND t4.N+1=t5.N  
LEFT JOIN #T t6 ON t5.lval=t6.lval AND t5.N+1=t6.N  
LEFT JOIN #T t7 ON t6.lval=t7.lval AND t6.N+1=t7.N  
LEFT JOIN #T t8 ON t7.lval=t8.lval AND t7.N+1=t8.N  
LEFT JOIN #T t9 ON t8.lval=t9.lval AND t8.N+1=t9.N  
LEFT JOIN #T t10 ON t9.lval=t10.lval AND t9.N+1=t10.N  
LEFT JOIN #T t11 ON t10.lval=t11.lval AND t10.N+1=t11.N  
LEFT JOIN #T t12 ON t11.lval=t12.lval AND t11.N+1=t12.N  
LEFT JOIN #T t13 ON t12.lval=t13.lval AND t12.N+1=t13.N  
LEFT JOIN #T t14 ON t13.lval=t14.lval AND t13.N+1=t14.N  
LEFT JOIN #T t15 ON t14.lval=t15.lval AND t14.N+1=t15.N  
LEFT JOIN #T t16 ON t15.lval=t16.lval AND t15.N+1=t16.N  
LEFT JOIN #T t17 ON t16.lval=t17.lval AND t16.N+1=t17.N  
LEFT JOIN #T t18 ON t17.lval=t18.lval AND t17.N+1=t18.N  
LEFT JOIN #T t19 ON t18.lval=t19.lval AND t18.N+1=t19.N  
LEFT JOIN #T t20 ON t19.lval=t20.lval AND t19.N+1=t20.N  
LEFT JOIN #T t21 ON t20.lval=t21.lval AND t20.N+1=t21.N  
LEFT JOIN #T t22 ON t21.lval=t22.lval AND t21.N+1=t22.N  
LEFT JOIN #T t23 ON t22.lval=t23.lval AND t22.N+1=t23.N  
LEFT JOIN #T t24 ON t23.lval=t24.lval AND t23.N+1=t24.N  
LEFT JOIN #T t25 ON t24.lval=t25.lval AND t24.N+1=t25.N  
LEFT JOIN #T t26 ON t25.lval=t26.lval AND t25.N+1=t26.N  
LEFT JOIN #T t27 ON t26.lval=t27.lval AND t26.N+1=t27.N  
LEFT JOIN #T t28 ON t27.lval=t28.lval AND t27.N+1=t28.N  
LEFT JOIN #T t29 ON t28.lval=t29.lval AND t28.N+1=t29.N  
LEFT JOIN #T t30 ON t29.lval=t30.lval AND t29.N+1=t30.N  
LEFT JOIN #T t31 ON t30.lval=t31.lval AND t30.N+1=t31.N  
LEFT JOIN #T t32 ON t31.lval=t32.lval AND t31.N+1=t32.N  
LEFT JOIN #T t33 ON t32.lval=t33.lval AND t32.N+1=t33.N  
LEFT JOIN #T t34 ON t33.lval=t34.lval AND t33.N+1=t34.N  
LEFT JOIN #T t35 ON t34.lval=t35.lval AND t34.N+1=t35.N  
LEFT JOIN #T t36 ON t35.lval=t36.lval AND t35.N+1=t36.N  
LEFT JOIN #T t37 ON t36.lval=t37.lval AND t36.N+1=t37.N  
LEFT JOIN #T t38 ON t37.lval=t38.lval AND t37.N+1=t38.N  
LEFT JOIN #T t39 ON t38.lval=t39.lval AND t38.N+1=t39.N  
LEFT JOIN #T t40 ON t39.lval=t40.lval AND t39.N+1=t40.N  
LEFT JOIN #T t41 ON t40.lval=t41.lval AND t40.N+1=t41.N  
LEFT JOIN #T t42 ON t41.lval=t42.lval AND t41.N+1=t42.N  
LEFT JOIN #T t43 ON t42.lval=t43.lval AND t42.N+1=t43.N  
LEFT JOIN #T t44 ON t43.lval=t44.lval AND t43.N+1=t44.N  
LEFT JOIN #T t45 ON t44.lval=t45.lval AND t44.N+1=t45.N  
LEFT JOIN #T t46 ON t45.lval=t46.lval AND t45.N+1=t46.N  
LEFT JOIN #T t47 ON t46.lval=t47.lval AND t46.N+1=t47.N  
LEFT JOIN #T t48 ON t47.lval=t48.lval AND t47.N+1=t48.N  
LEFT JOIN #T t49 ON t48.lval=t49.lval AND t48.N+1=t49.N  
LEFT JOIN #T t50 ON t49.lval=t50.lval AND t49.N+1=t50.N  
LEFT JOIN #T t51 ON t50.lval=t51.lval AND t50.N+1=t51.N  
LEFT JOIN #T t52 ON t51.lval=t52.lval AND t51.N+1=t52.N  
LEFT JOIN #T t53 ON t52.lval=t53.lval AND t52.N+1=t53.N  
LEFT JOIN #T t54 ON t53.lval=t54.lval AND t53.N+1=t54.N  
LEFT JOIN #T t55 ON t54.lval=t55.lval AND t54.N+1=t55.N  
LEFT JOIN #T t56 ON t55.lval=t56.lval AND t55.N+1=t56.N  
LEFT JOIN #T t57 ON t56.lval=t57.lval AND t56.N+1=t57.N  
LEFT JOIN #T t58 ON t57.lval=t58.lval AND t57.N+1=t58.N  
LEFT JOIN #T t59 ON t58.lval=t59.lval AND t58.N+1=t59.N  
LEFT JOIN #T t60 ON t59.lval=t60.lval AND t59.N+1=t60.N  
LEFT JOIN #T t61 ON t60.lval=t61.lval AND t60.N+1=t61.N  
LEFT JOIN #T t62 ON t61.lval=t62.lval AND t61.N+1=t62.N  
LEFT JOIN #T t63 ON t62.lval=t63.lval AND t62.N+1=t63.N  
LEFT JOIN #T t64 ON t63.lval=t64.lval AND t63.N+1=t64.N  
LEFT JOIN #T t65 ON t64.lval=t65.lval AND t64.N+1=t65.N  
LEFT JOIN #T t66 ON t65.lval=t66.lval AND t65.N+1=t66.N  
LEFT JOIN #T t67 ON t66.lval=t67.lval AND t66.N+1=t67.N  
LEFT JOIN #T t68 ON t67.lval=t68.lval AND t67.N+1=t68.N  
LEFT JOIN #T t69 ON t68.lval=t69.lval AND t68.N+1=t69.N  
LEFT JOIN #T t70 ON t69.lval=t70.lval AND t69.N+1=t70.N  
LEFT JOIN #T t71 ON t70.lval=t71.lval AND t70.N+1=t71.N  
LEFT JOIN #T t72 ON t71.lval=t72.lval AND t71.N+1=t72.N  
LEFT JOIN #T t73 ON t72.lval=t73.lval AND t72.N+1=t73.N  
LEFT JOIN #T t74 ON t73.lval=t74.lval AND t73.N+1=t74.N  
LEFT JOIN #T t75 ON t74.lval=t75.lval AND t74.N+1=t75.N  
LEFT JOIN #T t76 ON t75.lval=t76.lval AND t75.N+1=t76.N  
LEFT JOIN #T t77 ON t76.lval=t77.lval AND t76.N+1=t77.N  
LEFT JOIN #T t78 ON t77.lval=t78.lval AND t77.N+1=t78.N  
LEFT JOIN #T t79 ON t78.lval=t79.lval AND t78.N+1=t79.N  
LEFT JOIN #T t80 ON t79.lval=t80.lval AND t79.N+1=t80.N  
LEFT JOIN #T t81 ON t80.lval=t81.lval AND t80.N+1=t81.N  
LEFT JOIN #T t82 ON t81.lval=t82.lval AND t81.N+1=t82.N  
LEFT JOIN #T t83 ON t82.lval=t83.lval AND t82.N+1=t83.N  
LEFT JOIN #T t84 ON t83.lval=t84.lval AND t83.N+1=t84.N  
LEFT JOIN #T t85 ON t84.lval=t85.lval AND t84.N+1=t85.N  
LEFT JOIN #T t86 ON t85.lval=t86.lval AND t85.N+1=t86.N  
LEFT JOIN #T t87 ON t86.lval=t87.lval AND t86.N+1=t87.N  
LEFT JOIN #T t88 ON t87.lval=t88.lval AND t87.N+1=t88.N  
LEFT JOIN #T t89 ON t88.lval=t89.lval AND t88.N+1=t89.N  
LEFT JOIN #T t90 ON t89.lval=t90.lval AND t89.N+1=t90.N  
LEFT JOIN #T t91 ON t90.lval=t91.lval AND t90.N+1=t91.N  
LEFT JOIN #T t92 ON t91.lval=t92.lval AND t91.N+1=t92.N  
LEFT JOIN #T t93 ON t92.lval=t93.lval AND t92.N+1=t93.N  
LEFT JOIN #T t94 ON t93.lval=t94.lval AND t93.N+1=t94.N  
LEFT JOIN #T t95 ON t94.lval=t95.lval AND t94.N+1=t95.N  
LEFT JOIN #T t96 ON t95.lval=t96.lval AND t95.N+1=t96.N  
LEFT JOIN #T t97 ON t96.lval=t97.lval AND t96.N+1=t97.N  
LEFT JOIN #T t98 ON t97.lval=t98.lval AND t97.N+1=t98.N  
LEFT JOIN #T t99 ON t98.lval=t99.lval AND t98.N+1=t99.N  
LEFT JOIN #T t100 ON t99.lval=t100.lval AND t99.N+1=t100.N  
LEFT JOIN #T t101 ON t100.lval=t101.lval AND t100.N+1=t101.N  

where t1.N=1

8 авг 17, 13:25    [20708266]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Кесарь
Member

Откуда:
Сообщений: 453
iiyama, у меня аж глаз задёргался. В голову такое даже никогда не приходило!
8 авг 17, 13:30    [20708287]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
iiyama
Кесарь,
Людям со слабым сердцем не открывать
+ генерилку select-а напишите сами, тк она выходит за рамки изначальных условий :)



Коллега Вы собираетесь заджоинить 2^32 таблиц в запросе?

Кстати,
для SQL SERVER 2005
Tables per SELECT statement 256

для SQL 2016
Length of a string containing SQL statements (batch size) 65,536 * Network packet size
8 авг 17, 13:35    [20708302]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Может препод хотел, чтобы падаван сформировал строку через текстовый файл?
8 авг 17, 13:44    [20708325]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Что-то пошли решения другой задачи, не той, которая сформулирована в первом посте. Да?
8 авг 17, 13:48    [20708355]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
iiyama
Member

Откуда:
Сообщений: 642
Ну, ИМХО, ограничения из первого поста + добавленное позднее ограничение на CLR, выводит задачу из практической области в сферическую или в пятничную
8 авг 17, 14:06    [20708421]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3464
Владислав Колосов
Может препод хотел, чтобы падаван сформировал строку через текстовый файл?


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

8 авг 17, 14:26    [20708497]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
iii2
Member

Откуда:
Сообщений: 202
Rankatan
env
Rankatan,

в pivot ещё поверю, а вот с unpivot прошу показать

Да PIVOT, перепутал

С Pivot идея такая:
Ограничение на количество столбцов в результате там, если мне память не изменяет, 1024 (лень лезть проверять, честно говоря).
Поэтому, нумеруем значения в таблице, Делаем Pivot первых 1000 значений, складываем их.
Потом, соответственно, union all pivot следующей тысячи, тоже складываем.
Плодим секции Union All пока не надоест.
Потом pivot к результату, и складываем результаты предыдущих пивотов.

Лимит, правда, на количество складываемых значений не очень большой будет.
Ну 30, ну 100 тысяч...
9 авг 17, 09:13    [20709889]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
o-o
Guest
iii2
С Pivot идея такая:
Ограничение на количество столбцов в результате там, если мне память не изменяет, 1024 (лень лезть проверять, честно говоря).
Поэтому, нумеруем значения в таблице, Делаем Pivot первых 1000 значений, складываем их.
Потом, соответственно, union all pivot следующей тысячи, тоже складываем.
Плодим секции Union All пока не надоест.
Потом pivot к результату, и складываем результаты предыдущих пивотов.

Лимит, правда, на количество складываемых значений не очень большой будет.
Ну 30, ну 100 тысяч...

препод физ-ры уже готовит проигранное
9 авг 17, 09:56    [20709999]     Ответить | Цитировать Сообщить модератору
 Re: Значения чере запятую  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
iii2
С Pivot идея такая:
Ограничение на количество столбцов в результате там, если мне память не изменяет, 1024 (лень лезть проверять, честно говоря).
Поэтому, нумеруем значения в таблице, Делаем Pivot первых 1000 значений, складываем их.
Потом, соответственно, union all pivot следующей тысячи, тоже складываем.
Плодим секции Union All пока не надоест.
Потом pivot к результату, и складываем результаты предыдущих пивотов.

Лимит, правда, на количество складываемых значений не очень большой будет.
Ну 30, ну 100 тысяч...


Если PIVOT-ить группами, например по 16 строк, то 8-ю последовательными pivot-ами можно решить задачу для любого кол-ва строк (если, конечно, результирующий список через зпт влезет в VARCHAR(MAX)).

В приведенном ниже запросе для ясности используется CTE, но их легко можно перевести в подзапросы, поскольку CTE не рекурсивны и последовательны (каждый последующий CTE использует только предыдущий).
+ страшный запрос

DECLARE @t TABLE ( name varchar(1), id int)

Insert into @t(name,id) values ('a',1)
Insert into @t(name,id) values ('a',2)
Insert into @t(name,id) values ('a',2) -- добавим повтор
Insert into @t(name,id) values ('a',3)
Insert into @t(name,id) values ('a',10)
Insert into @t(name,id) values ('a',22)
Insert into @t(name,id) values ('a',24)
Insert into @t(name,id) values ('a',23)
Insert into @t(name,id) values ('a',44)
Insert into @t(name,id) values ('a',3347)
Insert into @t(name,id) values ('a',4)
Insert into @t(name,id) values ('a',34634)
Insert into @t(name,id) values ('a',44)
Insert into @t(name,id) values ('a',666)
Insert into @t(name,id) values ('a',667)
Insert into @t(name,id) values ('a',668)
Insert into @t(name,id) values ('a',669)
Insert into @t(name,id) values ('a',670)
Insert into @t(name,id) values ('a',671)
Insert into @t(name,id) values ('a',672)
Insert into @t(name,id) values ('a',673)
Insert into @t(name,id) values ('a',674)
Insert into @t(name,id) values ('a',675)
Insert into @t(name,id) values ('a',676)
Insert into @t(name,id) values ('a',677)
Insert into @t(name,id) values ('a',678)
Insert into @t(name,id) values ('a',679)
Insert into @t(name,id) values ('a',680)
Insert into @t(name,id) values ('a',681)
Insert into @t(name,id) values ('a',682)
Insert into @t(name,id) values ('a',683)
Insert into @t(name,id) values ('a',684)
Insert into @t(name,id) values ('a',685)
Insert into @t(name,id) values ('a',686)
Insert into @t(name,id) values ('a',687)
Insert into @t(name,id) values ('a',688)
Insert into @t(name,id) values ('a',689)
Insert into @t(name,id) values ('b',1)
Insert into @t(name,id) values ('b',4)

;
WITH x0 AS 
(
	SELECT name, CAST(REPLICATE(CAST(id AS VARCHAR(MAX)) + ',', COUNT(*)-1) + CAST(id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS txt, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) - 1 AS rn 
		FROM @t GROUP BY name, id
)
, x1 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x0
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)
, x2 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x1
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)
, x3 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x2
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)
, x4 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x3
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)
, x5 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x4
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)
, x6 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x5
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)
, x7 AS 
(
SELECT LEFT(name, CHARINDEX('/', name) - 1) AS name,
      [0] 
	+ CASE WHEN [1] IS NULL THEN '' ELSE ','+[1] END
	+ CASE WHEN [2] IS NULL THEN '' ELSE ','+[2] END
	+ CASE WHEN [3] IS NULL THEN '' ELSE ','+[3] END
	+ CASE WHEN [4] IS NULL THEN '' ELSE ','+[4] END
	+ CASE WHEN [5] IS NULL THEN '' ELSE ','+[5] END
	+ CASE WHEN [6] IS NULL THEN '' ELSE ','+[6] END
	+ CASE WHEN [7] IS NULL THEN '' ELSE ','+[7] END
	+ CASE WHEN [8] IS NULL THEN '' ELSE ','+[8] END
	+ CASE WHEN [9] IS NULL THEN '' ELSE ','+[9] END
	+ CASE WHEN [10] IS NULL THEN '' ELSE ','+[10] END
	+ CASE WHEN [11] IS NULL THEN '' ELSE ','+[11] END
	+ CASE WHEN [12] IS NULL THEN '' ELSE ','+[12] END
	+ CASE WHEN [13] IS NULL THEN '' ELSE ','+[13] END
	+ CASE WHEN [14] IS NULL THEN '' ELSE ','+[14] END
	+ CASE WHEN [15] IS NULL THEN '' ELSE ','+[15] END AS txt,
	CAST(SUBSTRING(name, CHARINDEX('/', name) + 1, 50) AS BIGINT) AS rn
FROM (
	SELECT name = name+'/'+CAST(rn/16 AS VARCHAR(20)), txt, rn%16 AS rn FROM 
	x6
) src
PIVOT(MAX(txt)
FOR rn IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) AS pvt
)

SELECT * FROM x7



Основная идея в том, что pivot группируется по name+'/'+CAST(rn/16 AS VARCHAR(20))

Данный запрос имеет два недостатка, которые, впрочем, несложно устранить:
1. Не учитывается возможное значение NULL в поле name
2. Не учитывается возможное значение NULL в поле id
9 авг 17, 10:31    [20710120]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить