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

Откуда: Белгород
Сообщений: 458
Используя pivot|unpivot

Имеем:
тестовые данные в следующем виде:
select * from 
	(
		select '+' as a, '-' as b, '~' as c, '+' as d, '-' as e, '~' as f, '+' as g, '-' as h, '~' as i union all
		select 		
		cast(zzz.a as varchar(100)) as a, 
		cast(zzz.b as varchar(100)) as b, 
		cast(zzz.c as varchar(100)) as c, 
		cast(zzz.d as varchar(100)) as d, 
		cast(zzz.e as varchar(100)) as e, 
		cast(zzz.f as varchar(100)) as f, 
		cast(zzz.g as varchar(100)) as g, 
		cast(zzz.h as varchar(100)) as h, 
		cast(zzz.i as varchar(100)) as i 
		 from 
		(
				select 
				111 as a,
				222 as b,
				333 as c,
				444 as d,
				555 as e,
				666 as f,
				777 as g,
				888 as h,
				999 as i				
		)zzz
	)ppp

Получаем данные ввиде:
Колонки: a| b| c| d| e| f| g| h| i|
Строка 1) + | -| ~| +| -| ~| +| -| ~|
Строка 2) 111| 222| 333| 444| 555| 666| 777| 888| 999

Если же выполнить:
select *	
FROM 
	(
	select * from 
	(
		select '+' as a, '-' as b, '~' as c, '+' as d, '-' as e, '~' as f, '+' as g, '-' as h, '~' as i union all
		select 		
		cast(zzz.a as varchar(100)) as a, 
		cast(zzz.b as varchar(100)) as b, 
		cast(zzz.c as varchar(100)) as c, 
		cast(zzz.d as varchar(100)) as d, 
		cast(zzz.e as varchar(100)) as e, 
		cast(zzz.f as varchar(100)) as f, 
		cast(zzz.g as varchar(100)) as g, 
		cast(zzz.h as varchar(100)) as h, 
		cast(zzz.i as varchar(100)) as i 
		 from 
		(
				select 
				111 as a,
				222 as b,
				333 as c,
				444 as d,
				555 as e,
				666 as f,
				777 as g,
				888 as h,
				999 as i				
		)zzz
	)ppp
) AS t
UNPIVOT (
  aData FOR fields in (a, b, c, d, e, f, g, h, i)
) AS unpvt

То получим вертикальное представление данных:
aData | Fields
--------------
+ |a
- |b
~ |c
+ |d
- |e
~ |f
+ |g
- |h
~ |i
111 |a
222 |b
333 |c
444 |d
555 |e
666 |f
777 |g
888 |h
999 |i

Подскажите пожалуйста, что необходимо предпринять чтоб получить вывод в следующем виде???:
+ | - | ~
111| 222| 333|
444| 555| 666|
777| 888| 999|

Заранее благодарен!
Внешне с использованием pivot чтото никак не получается...
18 окт 12, 12:31    [13339664]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Добрый Э - Эх
Guest
за-PIVOT-ить UNPIVOT
18 окт 12, 12:37    [13339721]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Алексей Кр
Member

Откуда: Белгород
Сообщений: 458
Можно поподробнее?
А то никак не получается (((
и анПивот делаю и внешне пивот делаю и ни в какую (((
18 окт 12, 12:40    [13339740]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Добрый Э - Эх
Guest
Алексей Кр
Можно поподробнее?
А то никак не получается (((
и анПивот делаю и внешне пивот делаю и ни в какую (((


Покажи, как делаешь.
18 окт 12, 12:44    [13339772]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Алексей Кр
Member

Откуда: Белгород
Сообщений: 458
select *
from (
	select *	
	FROM 
		(
		select * from 
		(
			select '+' as a, '-' as b, '~' as c, '+' as d, '-' as e, '~' as f, '+' as g, '-' as h, '~' as i union all
			select 		
			cast(zzz.a as varchar(100)) as a, 
			cast(zzz.b as varchar(100)) as b, 
			cast(zzz.c as varchar(100)) as c, 
			cast(zzz.d as varchar(100)) as d, 
			cast(zzz.e as varchar(100)) as e, 
			cast(zzz.f as varchar(100)) as f, 
			cast(zzz.g as varchar(100)) as g, 
			cast(zzz.h as varchar(100)) as h, 
			cast(zzz.i as varchar(100)) as i 
			 from 
			(
					select 
					111 as a,
					222 as b,
					333 as c,
					444 as d,
					555 as e,
					666 as f,
					777 as g,
					888 as h,
					999 as i				
			)zzz
		)ppp
	) AS t
	UNPIVOT (
	  aData FOR fields in (a, b, c, d, e, f, g, h, i)
	) AS unpvt

)tt
pivot (max(aData) for fields in ([+], [-], [~], a, b, c, d, e, f, g, h, i)
)pvt
18 окт 12, 12:47    [13339789]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Добрый Э - Эх
Guest
Вот так попробуй:
select pvt.[0],pvt.[1],pvt.[2]
from (
	select distinct unpvt.adata, 
               (dense_rank() over(order by adata) -1)%3 as rn,
               ceiling(dense_rank() over(order by adata)/3.0) rn1
	FROM 
		(
		select * from 
		(
			select '+' as a, '-' as b, '~' as c, '+' as d, '-' as e, '~' as f, '+' as g, '-' as h, '~' as i union all
			select 		
			cast(zzz.a as varchar(100)) as a, 
			cast(zzz.b as varchar(100)) as b, 
			cast(zzz.c as varchar(100)) as c, 
			cast(zzz.d as varchar(100)) as d, 
			cast(zzz.e as varchar(100)) as e, 
			cast(zzz.f as varchar(100)) as f, 
			cast(zzz.g as varchar(100)) as g, 
			cast(zzz.h as varchar(100)) as h, 
			cast(zzz.i as varchar(100)) as i 
			 from 
			(
					select 
					111 as a,
					222 as b,
					333 as c,
					444 as d,
					555 as e,
					666 as f,
					777 as g,
					888 as h,
					999 as i				
			)zzz
		)ppp
	) AS t
	UNPIVOT (
	  aData FOR fields in (a, b, c, d, e, f, g, h, i)
	) AS unpvt

)tt
pivot (max(aData) for rn in ([0], [1], [2])
)pvt
18 окт 12, 13:08    [13339968]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Алексей Кр
Member

Откуда: Белгород
Сообщений: 458
Спасибо большое! Получилось! Вообще здорово! :)
18 окт 12, 13:44    [13340321]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Алексей Кр
Member

Откуда: Белгород
Сообщений: 458
Единственный момент

Должен быть вид следующий
+ | - | ~
111| 222| 333|
444| 555| 666|
777| 888| 999|

Но получается:
- | ~ | +
111| 222| 333|
444| 555| 666|
777| 888| 999|

Это очень важно... т.к. тестовый пример спроецирован от целого SQL запроса по выборке данных...
18 окт 12, 13:50    [13340380]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Добрый Э - Эх
Guest
Алексей Кр,
Добавить в результат базового запроса, данные которого анпивотятся и пивотятся, идентификатор секции UNION ALL, и изменить секцию сортировки в аналитических ранках:
+
select distinct pvt.[0],pvt.[1],pvt.[2]
from (
	select  unpvt.adata, 
               (dense_rank() over(order by line_num, fields) -1)%3 as rn,
               ceiling(dense_rank() over(order by line_num, fields)/3.0) rn1
	FROM 
		(
		select * from 
		(
			select 1 as line_num, '+' as a, '-' as b, '~' as c, '+' as d, '-' as e, '~' as f, '+' as g, '-' as h, '~' as i union all
			select 2 as line_num,
			cast(zzz.a as varchar(100)) as a, 
			cast(zzz.b as varchar(100)) as b, 
			cast(zzz.c as varchar(100)) as c, 
			cast(zzz.d as varchar(100)) as d, 
			cast(zzz.e as varchar(100)) as e, 
			cast(zzz.f as varchar(100)) as f, 
			cast(zzz.g as varchar(100)) as g, 
			cast(zzz.h as varchar(100)) as h, 
			cast(zzz.i as varchar(100)) as i 
			 from 
			(
					select 
					111 as a,
					222 as b,
					333 as c,
					444 as d,
					555 as e,
					666 as f,
					777 as g,
					888 as h,
					999 as i				
			)zzz
		)ppp
	) AS t
	UNPIVOT (
	  aData FOR fields in (a, b, c, d, e, f, g, h, i)
	) AS unpvt

)tt
pivot (max(aData) for rn in ([0], [1], [2])
)pvt


Вроде должно работать
18 окт 12, 17:40    [13342422]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
user89
Member

Откуда:
Сообщений: 2083
Алексей Кр,

еще вариантик. Если данных много, то вместо ;with tmp лучше использовать временную таблицу #Tmp с индексом.

-- Специально поменяем местами '+' и '-' и пусть данные не симметричны
declare @t table (a varchar(50), b varchar(50), c varchar(50), d varchar(50), e varchar(50), f varchar(50), g varchar(50), h varchar(50), i varchar(50))
insert @t values ('+', '-', '~', '+', '-', '~', '-', '+', '~') insert @t values (111, null, 333, 444, 555, null, 888, 777, 999)
insert @t values (11111, 22222, 33333, 44444, 55555, 66666, 88888, 77777, null)
select * from @t

;with tmp as (
  select unp.val1, unp.val2
  from @t
  cross apply (values(a,'a'),(b,'b'),(c,'c'),(d,'d'),(e,'e'),(f,'f'),(h,'h'),(g,'g'),(i,'i')) unp(val1,val2)
), GetVal as (
  select t1.val1 [sim], t2.val1 [val], row_number() over(partition by t1.val1 order by t1.val1) [rn]
  from tmp t1
  inner join tmp t2 on t1.val2 = t2.val2
  where t1.val1 in ('+','-','~') and t2.val1 not in ('+','-','~')
)
select [+], [-], [~] from GetVal
pivot (max(val) for sim in ([+], [-], [~])) pvt
18 окт 12, 19:15    [13342872]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Алексей Кр
Member

Откуда: Белгород
Сообщений: 458
Спасибо огромное за советы! :)

Еще многому чему учиться!

Вопрос вдогонку... как считаете нормальная ситуация если таблица будет из одной записи но с 600-700 столбцами?

Т.к. в один прогон выборка данных происходит значительно быстрее, чем множество однотипных прогонов select или набор select'ов объединенных с union all...
18 окт 12, 20:16    [13343094]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
petek
Member

Откуда:
Сообщений: 2
Здравствуйте! Я только начинаю изучать SQL. Мне тоже нужно развернуть таблицу, но я не знаю как. Допустим есть простой запрос с двумя колонками и неизвестным числом строк:
SELECT c1, c2
FROM x

Получаем результат в виде:
c1c2
1a
1b
1c
2d
2e
3f
3g
3h
3i
......

Нужно развернуть таблицу, чтобы получилось так:
c1c2c2c2c2...
1abc ...
2de ...
3fghi...

Помогите, пожалуйста!
19 окт 12, 09:01    [13344301]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
petek
Member

Откуда:
Сообщений: 2
Или проще вариант, из таблицы
c1c2
1a
1b
1c
......

получить таблицу
c1c2c2c2...
1abc...
19 окт 12, 09:40    [13344506]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Добрый Э - Эх
Guest
petek,

ну во первых, количество колонок в таблице / результирующем наборе данных - строго лимитировано. Во вторых, как ни старайся, а в статике можно работать только с заранее известным набором полей (колонок). Если число колонок плавающее и заранее неизвестно, то тут уже нужно смотреть в сторону динамических запросов. Но в любом случае, нужно помнить, что кол-во колонок - ограниченно.
19 окт 12, 10:15    [13344730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста развернуть таблицу...  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 27
Алексей Кр,

https://www.sql.ru/forum/actualthread.aspx?tid=935641

тоже сталкивался с похожей проблемой. В прямом виде не пользовать (у меня сейчас через xml данные передаются).
13 ноя 12, 09:03    [13462638]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить