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

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

есть таблица заголовков с 'recht', 'land'


CREATE TABLE [dbo].[__test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Film_ID] [int] NULL,
	[Originaltitel] [varchar](200) NULL,
	[LAND] [varchar](200) NULL,
	[RECHT] [varchar](1000) NULL,
	[color] [varchar](10) NULL,
	[Bewertung] [int] NULL
) ON [PRIMARY]

GO

--truncate table __test

INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('23', 'Barbara Wood: Traumzeit',  'Malaysia', 'Pay-TV : 18.10.2003 - 31.12.2999 Free-VoD : 18.10.2003 - 31.12.2999 Pay-VoD : 18.10.2003 - 31.12.2999', 'green', '100') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('23', 'Barbara Wood: Traumzeit',  'Nepal', 'Pay-TV : 18.10.2003 - 31.12.2999 Free-VoD : 18.10.2003 - 31.12.2999 Pay-VoD : 18.10.2003 - 31.12.2999', 'green', '100') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('23', 'Barbara Wood: Traumzeit',  'Pakistan', 'Pay-TV : 18.10.2003 - 31.12.2999 Free-VoD : 18.10.2003 - 31.12.2999 Pay-VoD : 18.10.2003 - 31.12.2999', 'green', '100') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4070', 'Licht am Horizont',  'Malaysia', 'Free-TV : 03.01.2004 - 31.12.2999', 'red', '20') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4070', 'Licht am Horizont',  'Malaysia', 'Pay-VoD : 03.01.2004 - 31.12.2999', 'yellow', '40') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4070', 'Licht am Horizont',  'Nepal', 'Pay-TV : 03.01.2004 - 31.12.2999', 'red', '25') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4070', 'Licht am Horizont', 'Nepal', 'Free-VoD : 03.01.2004 - 31.12.2999', 'yellow', '45') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4070', 'Licht am Horizont', 'Pakistan', 'Free-TV : 03.01.2004 - 31.12.2999', 'red', '20') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4070', 'Licht am Horizont',  'Pakistan', 'Pay-VoD : 03.01.2004 - 31.12.2999', 'yellow', '40') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld',  'Malaysia', 'Pay-VoD : 16.03.2005 - 31.12.2999Free-VoD : 16.03.2005 - 31.12.2999', 'green', '95') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld',  'Malaysia', 'Pay-TV : 16.03.2005 - 31.12.2999', 'red', '20') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld', 'Nepal', 'Pay-TV : 16.03.2005 - 31.12.2999Pay-VoD : 16.03.2005 - 31.12.2999', 'green', '96') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld', 'Nepal', 'Pay-VoD : 16.03.2005 - 31.12.2999', 'red', '15') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Free-VoD : 16.03.2005 - 31.12.2999Pay-VoD : 16.03.2005 - 31.12.2999 FOUR', 'green', '98') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Pay-TV : 16.03.2005 - 31.12.2999 ONE ', 'red', '13') 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Pay-TV : 16.03.2005 - 31.12.2999   aaa  TWO', NULL, NULL) 
INSERT INTO  [__test]([Film_ID],[Originaltitel],[LAND],[RECHT],[color],[Bewertung]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Pay-TV : 16.03.2005 - 31.12.2999  aaaaaa  THREE', NULL, NULL) 




хочу транспонировать чтоб выводила все возможные значения 'recht' для заголовка с каждой 'land'
если попробовать pivot -ировать
SELECT * FROM (SELECT [Land], [Originaltitel], [Recht] FROM  __test) as source
PIVOT(MAX([Recht]) FOR [Land] in ( [Malaysia],[Nepal],[Pakistan])) as pvt
UNION
SELECT * FROM(SELECT [Land], [Originaltitel], [Recht] FROM __test) as source
PIVOT(MIN([Recht]) FOR [Land] in ( [Malaysia],[Nepal],[Pakistan])) as pvt

то вылетают промежуточные значения

например
'Pay-TV 222222222 TWO'
'Pay-TV 3333333333333 THREE'
для заголовка 'Schweigegeld' с land ='Pakistan'

есть ли способ чтоб выводить все в приведеном формате (нижняя таблица на скрин-шоте) ?

К сообщению приложен файл. Размер - 149Kb
23 дек 14, 15:30    [17040842]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Alex Brown,

пивот берет ключевую колонку и разворачивает в строку. У Вас на картинке что-то не то.
Например, было:
1 2
1 3
1 4

стало
1 2 3 4
23 дек 14, 15:42    [17040942]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Alex Brown
Member [заблокирован]

Откуда:
Сообщений: 776
Владислав Колосов,

INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('23', 'Barbara Wood: Traumzeit', 'Malaysia', 'Pay-TV   Free-VoD  Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('23', 'Barbara Wood: Traumzeit', 'Nepal', 'Pay-TV  Free-VoD Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('23', 'Barbara Wood: Traumzeit', 'Pakistan', 'Pay-TV  Free-VoD  Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4070', 'Licht am Horizont', 'Malaysia', 'Free-TV') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4070', 'Licht am Horizont', 'Malaysia', 'Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4070', 'Licht am Horizont', 'Nepal', 'Pay-TV') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4070', 'Licht am Horizont', 'Nepal', 'Free-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4070', 'Licht am Horizont', 'Pakistan', 'Free-TV') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4070', 'Licht am Horizont', 'Pakistan', 'Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Malaysia', 'Pay-VoD  Free-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Malaysia', 'Pay-TV') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Nepal', 'Pay-TV  Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Nepal', 'Pay-VoD') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Free-VoD Pay-VoD 444444444444 FOUR') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Pay-TV 111 ONE') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Pay-TV  222222222  TWO') 
INSERT INTO [__test]([Film_ID],[Originaltitel],[LAND],[RECHT]) VALUES('4257', 'Schweigegeld', 'Pakistan', 'Pay-TV   3333333333333  THREE') 


это данные в таблице с которой приведена выборка на рисунке с применением UNION и PIVOT.
на самом деле не знаю может ли PIVOT вообще быть применен в данном случае, т.к. текстовые данные нельзя корректно всунуть в какой нибуть агрегат.
я просто привел пример как должна выглядеть таблица и всунул пару значений в MIN i MAX чтоб как то вытянуть 2 разных значения, которые были вытянуты PIVOT-ом по длине строки.
2 промежуточных ( 222222222 TWO, 3333333333333 THREE) осталось не вытянутыми.
ищу подсказки как бытянуть все данные в виде транспонированной таблицы.
23 дек 14, 16:13    [17041172]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Alex Brown,

можно развернуть так:

SELECT distinct *
FROM
(
результат PIVOT:
23 'Malaysia' 'Nepal' 'Pakistan'
) tbl1
join __test on tbl1.F1_ID = __test.ID

если понятно, о чем я. А вот 'red' и 'yellow' потребуют дополнительного разворота и join, равно как и другие неповторяющиеся колонки в пределах ID. Может и есть более простой способ, но я его не знаю.
23 дек 14, 16:22    [17041242]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Alex Brown
Member [заблокирован]

Откуда:
Сообщений: 776
Владислав Колосов,

супер!!..
премного благодарен.
попробую полинковать осталньные колонки.
23 дек 14, 16:48    [17041469]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Alex Brown
Member [заблокирован]

Откуда:
Сообщений: 776
спивотировал таблицу, экспортировал в Excel. хочу покрасить как в дельфях.
можно сделать такой VB-скрипт v Excel чтоб красил ячейку по последней букве в ячейке?
намектите если кто делал VB-скрипты v Excel.

К сообщению приложен файл. Размер - 44Kb
30 дек 14, 12:58    [17072324]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alex Brown
намектите если кто делал VB-скрипты v Excel.

намекаю, форумы по VB и Excel расположены в другоих местах сайта
30 дек 14, 12:59    [17072331]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Alex Brown
Member [заблокирован]

Откуда:
Сообщений: 776
так выглядит экспорт в Excel

К сообщению приложен файл. Размер - 47Kb
30 дек 14, 13:00    [17072332]     Ответить | Цитировать Сообщить модератору
 Re: транспонировать таблицу с ПИВОТ..или есть еще какая то возможность?  [new]
Alex Brown
Member [заблокирован]

Откуда:
Сообщений: 776
Glory
Alex Brown
намектите если кто делал VB-скрипты v Excel.

намекаю, форумы по VB и Excel расположены в другоих местах сайта


хотел в продолжение топика вставить.
ладно продублирую раз делаю богохульство
30 дек 14, 13:02    [17072348]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить