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

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

Есть таблица emp
с числовыми полями U1, F1 и полем DT (дата), см скриншот.

нужно составить SQL-запрос так, чтобы значения столбца DT для записей каждой из пар U1, F1 транспонировались бы в строку с динамическим добавлением полей DT (DT_1, DT_2 ... DT_N), в результате должна получиться таблица result как на скриншоте.

пробовал смотреть в сторону Pivot, но пока не очень получается.
Подскажите пожалуйста как такое написать используя только язык запросов, возможно ли такое?

К сообщению приложен файл. Размер - 55Kb
12 янв 17, 00:45    [20096339]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Miko_v
пробовал смотреть в сторону Pivot, но пока не очень получается.
Подскажите пожалуйста как такое написать используя только язык запросов, возможно ли такое?
Открою маленький секрет: pivot -- это тоже "язык запросов". Так что смотрите в его сторону усерднее.
12 янв 17, 00:48    [20096351]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Miko_v
Member

Откуда:
Сообщений: 35
 		Гавриленко Сергей Алексеевич,

да уже немного вкурсе :)
просто пока непонятно как использовать этот оператор для решения этой задачи
12 янв 17, 00:58    [20096375]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
o-o
Guest 		вот доступно изложено, с примерами:
Using PIVOT and UNPIVOT
когда фиксированные строки научитесь разворачивать,
в динамике соберете и произвольное число столбцов.
если все же это "недоступно",
смотрите в сторону отчетных систем,
любой построитель отчетов разворачивает строки(произвольное число) в столбцы за пару кликов
12 янв 17, 01:10    [20096410]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
aleks2
Guest
Miko_v
Гавриленко Сергей Алексеевич,

да уже немного вкурсе :)
просто пока непонятно как использовать этот оператор для решения этой задачи


Для ЭТОЙ - Pivot бесполезен. Для Pivot нужен фиксированный и конечный набор значений столбца.

Его, конечно, можно прикрутить, пронумеровав даты.
Но проще сделать и заполнить временную таблицу.
12 янв 17, 08:18    [20096594]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
o-o
Guest 		Да, pivot по номеру даты, который надо посчитать
12 янв 17, 09:58    [20096915]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Miko_v
Member

Откуда:
Сообщений: 35
 		Всем - большое спасибо за советы, буду думать.
12 янв 17, 10:46    [20097156]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Гость проходящий мимо
Guest 		Miko_v,
где-то так

CREATE TABLE #EXAMPLE (U1 INT, F1 INT, DT DATE, count_NUMBER INT)

INSERT INTO #EXAMPLE (U1, F1 , DT, count_NUMBER)
SELECT U1, F1 , DT, ROW_NUMBER() OVER (PARTITION BY U1, F1 ORDER BY U1, F1)
FROM
(SELECT U1= 12, F1= 5, DT= '2017-01-05'
UNION ALL
SELECT U1= 12, F1= 5, DT= '2017-01-09'
UNION ALL
SELECT U1= 32, F1= 6, DT= '2017-01-11'
UNION ALL
SELECT U1= 32, F1= 8, DT= '2017-01-03'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-01'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-10'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-25') AS VR

CREATE TABLE #NUMBERS (NUMBER INT)
INSERT INTO #NUMBERS (NUMBER)
SELECT DISTINCT count_NUMBER
FROM #EXAMPLE

DECLARE @cols AS nvarchar(MAX)
SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([NUMBER] AS nvarchar(10)) + ']'
FROM #NUMBERS

DECLARE @SQL NVARCHAR(MAX)
SET @SQL= 'SELECT * FROM (SELECT U1, F1, count_NUMBER, DT FROM #EXAMPLE) AS tablePrePivoting
PIVOT(MAX(DT) FOR count_NUMBER IN(' + @cols + N')) AS tablePrePivoting
'
EXEC (@SQL)


DROP TABLE #EXAMPLE
DROP TABLE #NUMBERS
12 янв 17, 11:08    [20097273]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Гость проходящий мимо
Guest 		Miko_v,

Если заинтересовало решение, вот окончательный вариант

CREATE TABLE #EXAMPLE (U1 INT, F1 INT, DT DATE, count_NUMBER INT)

INSERT INTO #EXAMPLE (U1, F1 , DT, count_NUMBER)
SELECT U1, F1 , DT, ROW_NUMBER() OVER (PARTITION BY U1, F1 ORDER BY U1, F1, DT)
FROM
(SELECT U1= 12, F1= 5, DT= '2017-01-05'
UNION ALL
SELECT U1= 12, F1= 5, DT= '2017-01-09'
UNION ALL
SELECT U1= 32, F1= 6, DT= '2017-01-11'
UNION ALL
SELECT U1= 32, F1= 8, DT= '2017-01-03'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-01'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-10'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-25'
UNION ALL
SELECT U1= 32, F1= 9, DT= '2017-01-27') AS VR ORDER BY U1, F1 , DT

CREATE TABLE #NUMBERS (NUMBER INT)
INSERT INTO #NUMBERS (NUMBER)
SELECT DISTINCT count_NUMBER
FROM #EXAMPLE

DECLARE @cols AS nvarchar(MAX), @cols_NUMB NVARCHAR(MAX)
SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([NUMBER] AS nvarchar(10)) + ']', @cols_NUMB = ISNULL(@cols_NUMB + ',[', '[') + CAST([NUMBER] AS nvarchar(10)) + '] AS DT_'+ CAST([NUMBER] AS nvarchar(10))
FROM #NUMBERS

DECLARE @SQL NVARCHAR(MAX)
SET @SQL= 'SELECT U1, F1, '+@cols_NUMB+' FROM (SELECT TOP 100 PERCENT U1, F1, DT, count_NUMBER FROM #EXAMPLE ORDER BY U1, F1, DT) AS tablePrePivoting
PIVOT(MIN(DT) FOR count_NUMBER IN(' + @cols + N')) AS tablePrePivoting ORDER BY U1, F1
'
PRINT @SQL
EXEC (@SQL)


DROP TABLE #EXAMPLE
DROP TABLE #NUMBERS
12 янв 17, 11:47    [20097607]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Alex.C
Member

Откуда:
Сообщений: 33
 		Гость проходящий мимо,

спасибо за шаблон, сохранил, сам как-то давно делал подобное с помощью FOR XML для составления списка столбцов.
И почему интересно в SQL Server майкрософты не сделали PIVOT таким же "автоматически динамическим" как в Access, с помощью TRANSFORM?
12 янв 17, 15:22    [20098840]     Ответить | Цитировать Сообщить модератору
 Re: Sql запрос - транспонирование одного столбца в строку  [new]
Miko_v
Member

Откуда:
Сообщений: 35
 		Гость проходящий мимо,

Спасибо Вам большое! ушел пробовать :)
12 янв 17, 15:44    [20098988]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить