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

Откуда:
Сообщений: 62
Добрый день.

Есть таблица TBLdataIN примерно вот такого вида:
FIOBirthDateMaleSalaryAmount
Иванов Иван Иванович1980-06-01М50000.00
Петрова Петра Петровна1995-03-15Ж58000.00
Сидоров Сидр Сидорович1985-09-30М52000.00

и некий ID, который прилетает в качестве входного параметра в процедуру, в которой происходит сиё действо, поэтому можно считать его константой, и принять, ну, например, за 999.

Необходимо вставить в другую таблицу TBLdataOUT (в некий шлюз) данные из TBLdataIN в следующем виде:
ROWIDFIELDNAMEFIELDVALUECONSTFIELD
1999FIOИванов Иван Иванович0
1999BirthDate1980-06-010
1999MaleМ0
1999SalaryAmount50000.000
2999FIOПетрова Петра Петровна0
2999BirthDate1995-03-150
2999MaleЖ0
2999SalaryAmount58000.000
3999FIOСидоров Сидр Сидорович0
3999BirthDate1985-09-300
3999MaleМ0
3999SalaryAmount52000.000


То есть строки в исходной таблице необходимо развернуть по вертикали, причем одним из значений в итоговой таблице является название поля в исходной таблице. Еще пронумеровать по количеству записей в исходной. Что-то тут столько всего, что я немного растерялся, и даже не совсем понимаю с какого боку подступиться к задаче. Направьте, будьте так любезны.

Или может плюнуть и просто сделать кучку инсертов по числу полей в исходной таблице? Там получится где-то около 20-30 инсертов. Просто хотелось бы побыстрее, за один заход это сделать. Время доступа к таблице критично.

Под спойлером положил код для тестовой исходной таблицы.
+

--DROP table #TestTbl
CREATE TABLE #TestTbl(
	FIO varchar(100)
	,BirthDate date
	,Male varchar(1)
	,SalaryAmount money
) 

insert into #TestTbl
select 'Иванов Иван Иванович','19800601','М',50000
union
select 'Петрова Петра Петровна','19950315','Ж',58000
union
select 'Сидоров Сидр Сидорович','19850930','M',52000

select * from #TestTbl
2 июн 21, 16:30    [22330450]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
msLex
Member

Откуда:
Сообщений: 9058
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15


см пример с unpivot
2 июн 21, 16:35    [22330452]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
invm
Member

Откуда: Москва
Сообщений: 9778
RuCosinus,

drop table if exists #TestTbl;

CREATE TABLE #TestTbl(
	FIO varchar(100)
	,BirthDate date
	,Male varchar(1)
	,SalaryAmount money
) 

insert into #TestTbl
select 'Иванов Иван Иванович','19800601','М',50000
union
select 'Петрова Петра Петровна','19950315','Ж',58000
union
select 'Сидоров Сидр Сидорович','19850930','M',52000

select
 dense_rank() over (order by b.n) as [ROW],
 999,
 c.n.value('local-name(.)', 'sysname'),
 c.n.value('.', 'varchar(max)'),
 0
from
 (select * from #TestTbl for xml raw, type) a(x) cross apply
 a.x.nodes('/row') b(n) cross apply
 b.n.nodes('./@*') c(n);
2 июн 21, 16:45    [22330461]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
RuCosinus
Member

Откуда:
Сообщений: 62
msLex, ну я так и предполагал, что можно с помощью UNPIVOT, даже в тему вынес. Но если честно, никак он мне не дается, по MSDN-овским , да и другим примерам в интеренте, никак не ухвачу суть.

invm, точно, спасибо! Я сразу вспомнил, что уже использовал такой вариант, просто настолько редко приходится с этим сталкиваться, что плотно забывается.

Еще раз спасибо всем.
2 июн 21, 16:50    [22330466]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
uaggster
Member

Откуда:
Сообщений: 1030
invm
RuCosinus,

drop table if exists #TestTbl;

CREATE TABLE #TestTbl(
	FIO varchar(100)
	,BirthDate date
	,Male varchar(1)
	,SalaryAmount money
) 

insert into #TestTbl
select 'Иванов Иван Иванович','19800601','М',50000
union
select 'Петрова Петра Петровна','19950315','Ж',58000
union
select 'Сидоров Сидр Сидорович','19850930','M',52000

select
 dense_rank() over (order by b.n) as [ROW],
 999,
 c.n.value('local-name(.)', 'sysname'),
 c.n.value('.', 'varchar(max)'),
 0
from
 (select * from #TestTbl for xml raw, type) a(x) cross apply
 a.x.nodes('/row') b(n) cross apply
 b.n.nodes('./@*') c(n);

Вау! Это можно сделать полностью автоматически! invm, спасибо за пример.
Мне бы и в голову не пришло, честно говоря.
Я бы руками это сделал:
Select tt.* from (
select *, ROW_NUMBER() over (order by 1/0) [ROW], 999 [ID] from #TestTbl a) t
	Cross apply (Values (t.[ROW], t.[ID], 'FIO', Cast(t.FIO as sql_variant))
	,(t.[ROW], t.[ID], 'BirthDate', Cast(t.BirthDate as sql_variant))
	,(t.[ROW], t.[ID], 'Male', Cast(t.Male as sql_variant))
	,(t.[ROW], t.[ID], 'SalaryAmount', Cast(t.SalaryAmount as sql_variant))
	) tt ([ROW], [ID], [FIELDNAME],	[FIELDVALUE])

Но, кстати, на паре - тройке миллионов строк решение invm - должно сломаться, т.к. генерируемый на первом шаге xml - вылезет за 2 Гб. Ну и тормозить начнет еще раньше.
5 июн 21, 09:23    [22331701]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
invm
Member

Откуда: Москва
Сообщений: 9778
uaggster
Но, кстати, на паре - тройке миллионов строк решение invm - должно сломаться, т.к. генерируемый на первом шаге xml - вылезет за 2 Гб. Ну и тормозить начнет еще раньше.
Это довольно просто решается.
select
 dense_rank() over (order by t.rn) as [ROW],
 999,
 b.n.value('local-name(.)', 'sysname'),
 b.n.value('.', 'varchar(max)'),
 0
from
 (select *, row_number() over (order by 1/0) as rn from #TestTbl) t cross apply
 (select t.* for xml raw, type) a(x) cross apply
 a.x.nodes('/row/@*[local-name() != "rn"]') b(n);
5 июн 21, 11:21    [22331716]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
uaggster
Member

Откуда:
Сообщений: 1030
invm
uaggster
Но, кстати, на паре - тройке миллионов строк решение invm - должно сломаться, т.к. генерируемый на первом шаге xml - вылезет за 2 Гб. Ну и тормозить начнет еще раньше.
Это довольно просто решается.
(select t.* for xml raw, type) a(x) cross apply

Думаю, вот этот кусок может сломать запрос, если в исходных данных будет отбираться много записей, и размер результирующего xml превысит 2 Гб. Но это - не точно.
Наверное, можно было бы обойти этот момент, генерируя этот хмл "построчно", с привязкой к номеру строки, например.

Но всё это - не более, чем умозрительно. Сомневаюсь, что ТС собирается таким образом ворочать ~10 миллионные таблицы.
Как я уже сказал, оч. круто, спасибо.
7 июн 21, 08:33    [22332075]     Ответить | Цитировать Сообщить модератору
 Re: Как написать SELECT как бы транспонированной таблицы (UNPIVOT?)  [new]
invm
Member

Откуда: Москва
Сообщений: 9778
uaggster
Думаю, вот этот кусок может сломать запрос, если в исходных данных будет отбираться много записей, и размер результирующего xml превысит 2 Гб.
Тут только одна строка преобразуется в xml.
Так что вряд ли упрется в 2 Гб. Хотя умельцы найдутся всегда :)
7 июн 21, 10:04    [22332106]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить