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

Откуда:
Сообщений: 97
Добрый день всем участникам! Возник вопрос, который уже миллион раз наверное задавался, но не могу придумать ничего.

есть таблица
sum name monthyear
33797 "Алфа" апрель 2015
3300 "Алфа" январь 2015
90700 "ОРИ" ООО апрель 2015
4400 Иванов Иван февраль 2015
500 Иванов Иван март 2015
25000 Красота ООО январь 2015
3000 Старт ООО март 2015


КОЛИЧЕСТВО МЕСЯЦЕВ ЗАРАНЕЕ НЕИЗВЕСТНО

нужно получить такое:

nameянварь 2015 февраль 2015 март 2015 апрель 2015
"Алфа"3300 33797
"ОРИ" ООО90700
Иванов Иван 4400500
Красота ООО 25000
Старт ООО 3000


Попробовал через динамический pivot и в принципе получилось на тестовой базе, но на рабочей базе этот вариант не работает, ругается на совместимость базы данных, т.к. ее я трогать не могу, прошу у вас помощи в поиске другого решения. Как еще можно получить данные в таком виде. Спасибо!

Вот динамический pivot

DECLARE @ColumnNames AS NVARCHAR(MAX)
DECLARE @SQLQuery AS NVARCHAR(MAX)

 ;WITH ProductLines AS (
   select 
       d.monthyear
           from (select distinct monthyear,month,year from #Docs) as d  
)
SELECT @ColumnNames= ISNULL(@ColumnNames + ',','')
       + QUOTENAME(
                  ISNULL(
                         CAST(MonthYear AS VARCHAR(20))
                         ,'NULL'
                      )
              )
FROM ProductLines
order by MonthYear desc;

SELECT @ColumnNames AS 'ColumnNames'

 SET @SQLQuery =
N'select name, ' + @ColumnNames + ' 
from(select qty,Name,monthyear from #docs) as Sourcet PIVOT
(
sum(qty)
FOR monthyear  IN (' + @ColumnNames + '  )
) AS PivotTable;'

EXEC (@SQLQuery)



в принципе можно и такой вариант

namecol1col2col3col4
январь 2015 февраль 2015 март 2015 апрель 2015
"Алфа"3300 33797
"ОРИ" ООО90700
Иванов Иван 4400500
Красота ООО 25000
Старт ООО 3000
25 июн 15, 15:53    [17816137]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
iap
Member

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

Зачем неизвестное количество колонок - неясно.
Однако, надо выкинуть куда подальше голимый PIVOT -
что мешает писать старый добрый и более универсальный SUM(CASE WHEN THEN ELSE END)?
25 июн 15, 15:56    [17816152]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
sabitaidr
Member

Откуда:
Сообщений: 97
iap,
например в таблице, она здесь называется #Docs, может быть не только эти месяцы, еще может быть , например, октябрь 2014, ноябрь 2014, декабрь 2014... - все зависит от периода, который задать и соответственно клиентов, у которых были операции в это время.
через case имеете ввиду внутри перебирать все месяцы? такой вариант знаю, но у меня он динамическим должен быть
25 июн 15, 16:04    [17816187]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
TRANSFORM
Guest
sabitaidr,

1. https://www.sql.ru/faq/faq_topic.aspx?fid=358

2. https://www.sql.ru/faq/faq_topic.aspx?fid=571

3. https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1008462&msg=14005546
25 июн 15, 16:05    [17816198]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
sabitaidr
такой вариант знаю, но у меня он динамическим должен быть
И какие же трудности?
Особенно по сравнению с убожеством по имени PIVOT?
25 июн 15, 16:08    [17816219]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
sabitaidr
iap,
например в таблице, она здесь называется #Docs, может быть не только эти месяцы, еще может быть , например, октябрь 2014, ноябрь 2014, декабрь 2014... - все зависит от периода, который задать и соответственно клиентов, у которых были операции в это время.
через case имеете ввиду внутри перебирать все месяцы? такой вариант знаю, но у меня он динамическим должен быть


НУ и сформируйте динамически запрос с case-ами - без динамики так и не получится, учитывая неизвестное количество колонок.

P.S. Иметь разный уровень совместимости на тестовой и рабочей базе - значит не желать себе добра ;)
25 июн 15, 16:09    [17816222]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
a_voronin
Member

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

Для подобного рода запросов, где надо разворачивать данные не только по вертикали, но и по горизонтали существует язык MDX и технология OLAP. Если у вас единичный запрос -- вы можете сделать динамический PIVOT и развернуть ваши даты по горизонтали. Но если запрос такого рода возникает часто, то вам пора смотреть в направлении SSAS и OLAP-кубов.
25 июн 15, 16:11    [17816234]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
sabitaidr
Member

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

про динамические case не знал, попробую, если есть ссылка с описанием, то буду благодарен.
P.S. переходим на 2008 sql server, на тестовой он уже стоит, тестим его, а на рабочей пока не дошли до этого
25 июн 15, 16:15    [17816262]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
sabitaidr
про динамические case не знал
Непонятно. Что мешает сформировать ЛЮБОЙ скрипт
динамически?
Актуальный именно в данный момент.
25 июн 15, 16:18    [17816279]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
Glory
Member

Откуда:
Сообщений: 104760
sabitaidr
про динамические case не знал,

Нет никакого динамического case.
Есть просто динамически формируемый текст запроса. Который может включать все разрешенные команды и функции, в том числе и case.
25 июн 15, 16:18    [17816281]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
sabitaidr
Member

Откуда:
Сообщений: 97
TRANSFORM,
спасибо за варианты, но хотелось бы узнать может есть более простые варианты, чем создание процедур. Благодарю за помощь
25 июн 15, 16:18    [17816283]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
Glory
Member

Откуда:
Сообщений: 104760
sabitaidr
но хотелось бы узнать может есть более простые варианты, чем создание процедур

Процедура - это всего лишь именной скрипт, хранящийся на сервере
25 июн 15, 16:20    [17816289]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
sabitaidr
P.S. переходим на 2008 sql server, на тестовой он уже стоит, тестим его, а на рабочей пока не дошли до этого
Вообще-то уже SQL2016 тестируется...

P.S. Это мне простительно - на моём месте работы MSSQL активно давят Oracleом и SAPом
О переходе на новые версии MSSQL можно не заикаться.
Скоро придётся идти на биржу.
25 июн 15, 16:22    [17816313]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
iap, за что вам так PIVOT не нравится?

sabitaidr, вот вам два варианта - один - ваш, с PIVOT'ом (поправил его, потому что вы там обращаетесь к полям, которые не привели в примере), а второй - с sum(case...). Сортировка только страдает, надеюсь, найдете сами, где сортировку по году, месяцу сделать.

create table #docs ([sum] int, name nvarchar(4000), monthyear nvarchar(4000));

insert into #docs values (33797, '"Алфа"','апрель 2015'),
(3300, '"Алфа"','январь 2015'),
(90700, '"ОРИ" ООО','апрель 2015'),
(4400, 'Иванов Иван','февраль 2015'),
(500, 'Иванов Иван','март 2015'),
(25000, 'Красота ООО','январь 2015'),
(3000, 'Старт ООО','март 2015');

DECLARE @ColumnNames AS NVARCHAR(MAX);
DECLARE @SQLQuery AS NVARCHAR(MAX);

WITH ProductLines AS (
   select 
       d.monthyear
           from (select distinct monthyear from #Docs) as d  
)
SELECT @ColumnNames= ISNULL(@ColumnNames + ',','')
       + QUOTENAME(
                  ISNULL(
                         CAST(MonthYear AS VARCHAR(20))
                         ,'NULL'
                      )
              )
FROM ProductLines
order by MonthYear desc;

 SET @SQLQuery =
N'select name, ' + @ColumnNames + ' 
from(select [sum],Name,monthyear from #docs) as Sourcet PIVOT
(
sum([sum])
FOR monthyear  IN (' + @ColumnNames + '  )
) AS PivotTable;';

EXEC (@SQLQuery);

set @SQLQuery = N'select name';

select @SQLQuery = @SQLQuery + 
         N', sum(case when monthyear = ''' + monthyear + ''' then [sum] else null end) as [' + monthyear + ']'
from (select distinct monthyear from #Docs) as d
order by monthyear;

set @SQLQuery = @SQLQuery + N' from #docs group by Name;';

exec (@SQLQuery);

drop table #docs
25 июн 15, 16:28    [17816343]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
sabitaidr
Member

Откуда:
Сообщений: 97
iap,
Это уже не от меня зависит=)
25 июн 15, 16:31    [17816358]     Ответить | Цитировать Сообщить модератору
 Re: Неопределенное количество столбцов  [new]
sabitaidr
Member

Откуда:
Сообщений: 97
Minamoto,
Да, с сортировкой разберусь, спасибо большое! С case мне вариант вполне подошел
25 июн 15, 16:34    [17816376]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить