Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Немного о PIVOT  [new]
СТУДЕНТ123
Guest
Здрасти ГУРУ!

Я самоучка, дошел до PIVOT, но не совсем понимаю его превосходство над обычным запросом, за исключением отсутствия скалчрной функции и более краткого листинга, прокомментируюте пожалуйсто, спросить то больше не укого =(

CREATE TABLE #DATA (STORERKEY VARCHAR(10),SKU VARCHAR(10),QTY INT)

INSERT INTO #DATA
				SELECT '1','SKU1',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5

SELECT		SKU,SUM(CASE WHEN STORERKEY = '1' THEN QTY ELSE 0 END) AS '1',
				SUM(CASE WHEN STORERKEY = '2' THEN QTY ELSE 0 END) AS '2',
				SUM(CASE WHEN STORERKEY = '3' THEN QTY ELSE 0 END) AS '3'
FROM		#DATA
GROUP BY	SKU

-----------------
SELECT		SKU,[1],[2],[3]
FROM		( SELECT SKU,STORERKEY,QTY FROM #DATA)		AS SOURCETABLE 
PIVOT		( SUM(QTY) FOR STORERKEY IN ([1],[2],[3]))	AS PIVOTTABLE
-----------------
DROP TABLE #DATA
2 июл 15, 18:36    [17845225]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
Glory
Member

Откуда:
Сообщений: 104760
СТУДЕНТ123
но не совсем понимаю его превосходство над обычным запросом

более компактный текст запроса
2 июл 15, 18:38    [17845236]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
GLORY т.е. в данном примере только 2 приемущества:
1. в плане отсутствует лишний скалярный оператор
2. легко читается сам запрос

я првильно Вас понимаю?
2 июл 15, 18:40    [17845249]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
Glory
СТУДЕНТ123
но не совсем понимаю его превосходство над обычным запросом

более компактный текст запроса

еще вопросик: GLORY как правильно написать запрос с PIVOT если к примеру в колонке STORERKEY (хоть этого не может быть) будет запись состояния NULL

CREATE TABLE #DATA (STORERKEY VARCHAR(10),SKU VARCHAR(10),QTY INT)

INSERT INTO #DATA
				SELECT '1','SKU1',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '1','SKU1',5		
UNION ALL		SELECT '1','SKU2',5		UNION ALL		SELECT '1','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '2','SKU2',5		
UNION ALL		SELECT '2','SKU2',5		UNION ALL		SELECT '1','SKU3',5		
UNION ALL		SELECT '3','SKU3',5		UNION ALL		SELECT '3','SKU3',5		
UNION ALL		SELECT '1','SKU4',5		UNION ALL		SELECT '2','SKU4',5		
UNION ALL		SELECT '2','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT '3','SKU4',5		
UNION ALL		SELECT '3','SKU4',5		UNION ALL		SELECT null,'SKU4',5	

SELECT		SKU,SUM(CASE WHEN STORERKEY = '1' THEN QTY ELSE 0 END) AS '1',
				SUM(CASE WHEN STORERKEY = '2' THEN QTY ELSE 0 END) AS '2',
				SUM(CASE WHEN STORERKEY = '3' THEN QTY ELSE 0 END) AS '3',
				SUM(CASE WHEN STORERKEY is null THEN QTY ELSE 0 END) AS 'NULL'
FROM		#DATA
GROUP BY	SKU
-----------------

-----------------
SELECT		SKU,[1],[2],[3]
FROM		( SELECT SKU,STORERKEY,QTY FROM #DATA)		AS SOURCETABLE 
PIVOT		( SUM(QTY) FOR STORERKEY IN ([1],[2],[3]))	AS PIVOTTABLE
-----------------
DROP TABLE #DATA
2 июл 15, 18:47    [17845278]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
SELECT		SKU,[1],[2],[3],[NULL]
FROM		( SELECT SKU,ISNULL(STORERKEY,'NULL') AS STORERKEY,QTY FROM #DATA)		AS SOURCETABLE 
PIVOT		( SUM(QTY) FOR STORERKEY IN ([1],[2],[3],[NULL]))	AS PIVOTTABLE


Наверное так, если правильно =/
2 июл 15, 19:16    [17845369]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
Подскажите уважаемые, да бы закрыть у себя в голове тему про PIVOT!

вопрос: если заранее неизвестно количество уникальных значений в колонке STORERKEY (по которой необходимо делать группировку), то задача решается по средством динамического построения запроса? или возможно ее избежать? пример все тот же (который в самом начале)

Всем спасибо за ответы!
3 июл 15, 17:23    [17850092]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31420
СТУДЕНТ123
то задача решается по средством динамического построения запроса?
Да.
3 июл 15, 17:31    [17850127]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
alexeyvg
СТУДЕНТ123
то задача решается по средством динамического построения запроса?
Да.
СПАСИБО!!! +1 К КАРМЕ=) УШЕЛ УЧИТЬ ДАЛЬШЕ.
3 июл 15, 17:33    [17850139]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
СТУДЕНТ123
alexeyvg
пропущено...
Да.
СПАСИБО!!! +1 К КАРМЕ=) УШЕЛ УЧИТЬ ДАЛЬШЕ.


Если вам надо динамически развертывать данные по горизонтали, а не только по вертикали, надо смотреть в сторону OLAP кубов и MDX.
6 июл 15, 10:05    [17855986]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
СТУДЕНТ123,

без PIVOT намного универсальнее и понятнее. PIVOT - в топку! ИМХО, конечно же.
6 июл 15, 10:09    [17855999]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
iap
СТУДЕНТ123,

без PIVOT намного универсальнее и понятнее. PIVOT - в топку! ИМХО, конечно же.


Спасибо огромное за ответ, вопрос темы был исключительно академического характера, в боевых обстоятельствах при необходимости динамического построения - PIVOT пойдет лесом.
6 июл 15, 17:21    [17858589]     Ответить | Цитировать Сообщить модератору
 Re: Немного о PIVOT  [new]
СТУДЕНТ123
Guest
a_voronin
СТУДЕНТ123
пропущено...
СПАСИБО!!! +1 К КАРМЕ=) УШЕЛ УЧИТЬ ДАЛЬШЕ.


Если вам надо динамически развертывать данные по горизонтали, а не только по вертикали, надо смотреть в сторону OLAP кубов и MDX.

Спасибо за направление, как будет чуть больше времени обязательно уделю время кудам и MDX
6 июл 15, 17:22    [17858595]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить