Перегруппировка таблицы в mssql

@Nelia · Регистрация: 30.10.2012

Студворк — интернет-сервис помощи студентам

Допустим, есть таблица:

ID	NUM
5	56
5	34
8	52
4	53
3	42
3	11
3	44

Нужно сделать из этого таблицу вида:

ID	NUM1	NUM2	NUM3
3	11	42	44
4	53
5	34	56
8	52

На сколько я понимаю, можно было бы использовать массив. Задать ему размер с максимальным значением повторяющихся ID, после чего выбрать id, NUM1...NUM_MAX. Но, кажется, нет массивов в mssql. И наверняка есть способ гораздо проще, через group by, но мне не хватает пока знаний, чтобы это осуществить.
Заранее спасибо за помощь!

Кстати, почему-то не срабатывает bbcode на форуме, чтобы нарисовать таблицу. Сорри.

@pincet · 31.10.2012, 13:31

http://msdn.microsoft.com/ru-r... 05%29.aspx

@Nelia · 31.10.2012, 15:08 **[ТС]**

Сообщение от pincet

http://msdn.microsoft.com/ru-r... 05%29.aspx

Хороший оператор! Мерси

Но как изначально определить количество столбцов?

@pincet · 31.10.2012, 15:36

да, здесь засада. не увидел. Динамический SQL разве что

@Nelia · 31.10.2012, 15:43 **[ТС]**

То есть мне сначала нужно сделать преобразование таблицы:

ID	NUM
5	56
5	34
8	52
4	53
3	42
3	11
3	44

В таблицу вида:

ID	[B] NUM[B]	Ind.
5	56	NUM1
5	34	NUM2
8	52	NUM1
4	53	NUM1
3	42	NUM1
3	11	NUM2
3	44	NUM3

А потом можно и сделать pivot, кол-во колонок - по максимальному NUM.

Как сделать такое преобразование?

@pincet · 31.10.2012, 15:48

Почитай - http://kirillov-blog.blogspot.... erver.html

@Nelia · 31.10.2012, 15:49 **[ТС]**

Сообщение от pincet

да, здесь засада. не увидел. Динамический SQL разве что

да.. действительно.

@iap · 31.10.2012, 16:00

Сообщение от Nelia

Хороший оператор! Мерси

Но как изначально определить количество столбцов?

Сами же писали, что можно определить максимально возможное количество повторений.
Динамический SQL в такой ситуации будет не нужен.
Просто в непонадобившихся полях возвращать NULL

@pincet · 31.10.2012, 16:03

Весь вопрос в том, что все поля нужно перечислить явно.

@iap · 31.10.2012, 16:05

Сообщение от pincet

Весь вопрос в том, что все поля нужно перечислить явно.

А разве это так трудно? Ведь не вручную же!
В динамическом SQL это всё равно придётся делать так или иначе.

@pincet · 31.10.2012, 16:17

Что-то мне не совсем понятно, как в предложении SELECT перечислить не вручную произвольное (хотя и заранее известное) количество полей?

@iap · 31.10.2012, 16:50

Сообщение от pincet

Что-то мне не совсем понятно, как в предложении SELECT перечислить не вручную произвольное (хотя и заранее известное) количество полей?

Например, в MS SQL SMS в ObjectExplorer drag&dropом перетащить "columns" таблицы или представления в окно редактирования.

Или создать шаблон (template) вот с таким текстом, к примеру:

T-SQL
1
2
3
4
5
6
7
SELECT CASE ROW_NUMBER()OVER(ORDER BY[ORDINAL_POSITION])WHEN 1 THEN' 'ELSE','END+QUOTENAME([COLUMN_NAME])
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_SCHEMA]=N'<TABLE_SCHEMA,sysname,TABLE_SCHEMA>'AND[TABLE_NAME]=N'<TABLE_NAME,sysname,TABLE_NAME>'
AND [DATA_TYPE]<>N'timestamp'
AND COLUMNPROPERTY(OBJECT_ID('<TABLE_SCHEMA,sysname,TABLE_SCHEMA>.<TABLE_NAME,sysname,TABLE_NAME>','U'),[COLUMN_NAME],'IsComputed')=0
AND COLUMNPROPERTY(OBJECT_ID('<TABLE_SCHEMA,sysname,TABLE_SCHEMA>.<TABLE_NAME,sysname,TABLE_NAME>','U'),[COLUMN_NAME],'IsIdentity')=0
ORDER BY [ORDINAL_POSITION];

Достаточно вставить этот текст в окно редактирования скиптов, выделить его, нажать <Ctrl>-<Shift>-M, заполнить имена схемы и таблицы,
после чего выполнить, - и можно копипастить список полей куда надо.
Я там, правда, игнорировал поля IDENTITY, TIMESTAMP и вычисляемые,
ибо делал для скрипта на INSERT

@Devil_FoX · 31.10.2012, 19:03

Динамический Pivot: (переделывать под текущее задание нет времени - разбирайте сами код)

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
    DECLARE @R VARCHAR(5000), @ST VARCHAR(5000)
    SELECT @R = ISNULL(@R,'')+ CASE WHEN @R IS NULL THEN '' ELSE ', ' END + '['+cast (filid AS varchar(100))+']'
    FROM (  select distinct
                filid
            from #CTM ) a
 
SET @ST = '
select
    ppp.*
from(
        select
            lagerid, filid, Price
        from #CTM f ) a
pivot(
    avg(price)
    for filid in ('+ @R +')
    ) as ppp    '
EXEC (@ST)

Добавлено через 4 минуты
Админам - Можно ли мой скрипт кинуть в шапку форма MS SQL? Я уже несколько раз данный код кидал. Либо создать в шапке форума тему "Интересные задачи" или "Делимся наработками"

@Nelia · 13.11.2012, 18:21 **[ТС]**

Все-таки у меня бардак получается с этим динамическим pivot

Таблица:

ID	DATE1	DATE2	CLM
5	12.12.2011	12.06.2012	clm1
5	12.12.2011	12.06.2012	clm2
8	01.03.2011	04.04.2012	clm2
4	04.01.2011	01.01.2012	clm1

Мой неправильный код:

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create table #TSP (
 
    [ID] int,
    [D1] datetime,
    [D2] datetime,
    [CLM] int
)
 
insert into #TSP
select --запрос для таблицы, в силу длины и неважности не привожу--)
 
DECLARE @R VARCHAR(5000), @ST VARCHAR(5000)
    SELECT @R = ISNULL(@R,'')+ CASE WHEN @R IS NULL THEN '' ELSE ', ' END + '['+cast (ID AS varchar(100))+']'
    FROM (
            select DISTINCT ID
            from #TSP) a
 
SET @ST = '
select ppp.* from (
            select ?????
            from #TSP
                ) a
pivot(
    D1, D2, CLM
    for ID in ('+ @R +')
    ) as ppp '
EXEC (@ST)
 
--select * from #TSP
 
Drop table #TSP

что можно вставить вместо ?????, чтобы получить такую таблицу:

ID	FROM DATE_1	TILL DATE_1	CLM_1	FROM DATE_2	TILL DATE_2	CLM_2
5	12.12.2011	12.06.2012	clm1	12.12.2011	12.06.2012	clm2
8	01.03.2011	04.04.2012	clm2
4	04.01.2011	01.01.2012	clm1

Добавлено через 9 минут
где-то же я должна задать название колонок FROM DATE_1 TILL DATE_1 CLM_1 FROM DATE_2 TILL DATE_2 CLM_2

Добавлено через 22 минуты
Нет, что-то тут вообще не то. В вышеуказанном скрипте значения через запятую записываются.

Короче, подскажите, как этот динамический пивот приложить к моему случаю, пожалуйста.

Добавлено через 8 минут
Как мне пробежаться по всем значениям ID и определить количество будущих столбцов, используя #TSP? Потом, вероятно, надо как-то создать колонку в таблицу #TSP с пронумерованными значениями для каждого ID... и уже потом сделать этот пивот..., вот только и в сам пивот надо подставить то количество колонок, которое нужно. И я не знаю не одного из этих вопросов(((

help, pls

Добавлено через 2 часа 25 минут
Если нет такого способа.. Скажите, пжлста, как прочитать вот эту конструкцию

Code
1
2
3
4
    SELECT @R = ISNULL(@R,'')+ CASE WHEN @R IS NULL THEN '' ELSE ', ' END + '['+cast (ID AS varchar(100))+']'
    FROM (
            select DISTINCT ID
            from #TSP) a

что это штука делает? Ищу способ переменной передать результат селекта

@Kruds · 21.03.2013, 14:04

Не вполне понятно как заюзать pivot. В моем случае таблица такая:
id1 id2 value
1 1 "string1"
1 2 "string2"
2 1 "string3"
Плюс куча других значений, если отбросить поле value то строки будут одинаковые. Можно как-нибудь строки схлопнуть в одну не потеряв ни одного поля, а поле value странспонировать в строку и прикрепить к остальным данным?

Добавлено через 3 минуты
Использовал такое решение: поскольку на позицию с id1,id2 значений value может быть не больше 5 то с помощью left join 5 раз сцепил таблицу саму с собой, вытащил из первой таблицы все нужные столбцы, а из остальных значения value. На таблицах малого объема это может и сгодится, но данных как правило много и вопрос скорости выполнения запроса стоит мегаостро.

Добавлено через 4 часа 18 минут
Если кому-то понадобится: воспользовался решением Ice_and_Fire'а (Получение результатов выборки в виде строки) немного модифицировав. Работает, но наверное будет лучше использовать нечто более рациональное.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE #mytable (id1 INT, id2 INT, value1 VARCHAR(MAX), value2 VARCHAR(MAX), value3 VARCHAR(MAX), value4 VARCHAR(MAX), value5 VARCHAR(MAX))
DECLARE @Line VARCHAR(100) DECLARE  @Line1 VARCHAR(MAX) DECLARE  @Line2 VARCHAR(MAX) DECLARE  @Line3 VARCHAR(MAX) DECLARE  @Line4 VARCHAR(MAX) DECLARE  @Line5 VARCHAR(MAX)
DECLARE @id1 INT DECLARE @id2 INT
DECLARE Cur1 CURSOR READ_ONLY FOR SELECT DISTINCT id1, id2 FROM #table OPEN Cur1
FETCH NEXT FROM Cur1 INTO @id1, @id2
WHILE (@@FETCH_STATUS = 0)
BEGIN
        DECLARE Cur2 CURSOR READ_ONLY FOR SELECT VALUE FROM #itog1 WHERE id1= @id1 AND id2= @id2
        OPEN Cur2
        FETCH NEXT FROM Cur2 INTO @Line
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
                IF @@FETCH_STATUS = 0 BEGIN SET @Line1 = @Line FETCH NEXT FROM Cur2 INTO @Line END
                IF @@FETCH_STATUS = 0 BEGIN SET @Line2 = @Line FETCH NEXT FROM Cur2 INTO @Line END 
                IF @@FETCH_STATUS = 0 BEGIN SET @Line3 = @Line FETCH NEXT FROM Cur2 INTO @Line END 
                IF @@FETCH_STATUS = 0 BEGIN SET @Line4 = @Line FETCH NEXT FROM Cur2 INTO @Line END
                IF @@FETCH_STATUS = 0 BEGIN SET @Line5 = @Line FETCH NEXT FROM Cur2 INTO @Line END
        END CLOSE Cur2 DEALLOCATE Cur2
        INSERT INTO #mytable VALUES (@id1, @id2, @Line1, @Line2, @Line3, @Line4, @Line5)
        FETCH NEXT FROM Cur1 INTO @id1, @id2
END CLOSE Cur1 DEALLOCATE Cur1

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .	SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .
SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .	Инструменты COM: Сохранение данный из VARIANT в файл и загрузка из файла в VARIANT bedvit 28.01.2026 Сохранение базовых типов COM и массивов (одномерных или двухмерных) любой вложенности (деревья) в файл, с возможностью выбора алгоритмов сжатия и шифрования. Часть библиотеки BedvitCOM Использованы. . .	SDL3 для Android: Загрузка PNG с альфа-каналом с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 28.01.2026 Содержание блога SDL3 имеет собственные средства для загрузки и отображения PNG-файлов с альфа-каналом и базовой работы с ними. В этой инструкции используется функция SDL_LoadPNG(), которая. . .

@pincet 1654 / 1153 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	31.10.2012, 13:31
	http://msdn.microsoft.com/ru-r... 05%29.aspx 0

@pincet 1654 / 1153 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	31.10.2012, 15:36
	да, здесь засада. не увидел. Динамический SQL разве что 0

@pincet 1654 / 1153 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	31.10.2012, 15:48
	Почитай - http://kirillov-blog.blogspot.... erver.html 1

@pincet 1654 / 1153 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	31.10.2012, 16:03
	Весь вопрос в том, что все поля нужно перечислить явно. 0

@pincet 1654 / 1153 / 173 Регистрация: 23.07.2010 Сообщений: 6,910
	31.10.2012, 16:17
	Что-то мне не совсем понятно, как в предложении SELECT перечислить не вручную произвольное (хотя и заранее известное) количество полей? 0