ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужно транспонировать таблицу
14 сообщений из 14, страница 1 из 1
Нужно транспонировать таблицу
    #39599704
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
Добрый день!

есть две таблицы:

1) Таблица скидок
[Id_param] - id скидки
[Name] - название скидки

CREATE TABLE Params
(
[Id_param] smallint IDENTITY(1,1) not null,
[Name] nvarchar(50) not null
CONSTRAINT Id_params PRIMARY KEY CLUSTERED
(
[Id_param] ASC
)
)

INSERT Params (Name) values('Скидка Пенсионер')
INSERT Params (Name) values('Скидка прекрасного дня')
INSERT Params (Name) values('Скидка Счастливчик')
INSERT Params (Name) values('Скидка здравствуй')


2)Таблица товаров
[id_object] - id товара
[id_param] - скидки
[cdate] - дата действия скидки
[value] цена товара

CREATE TABLE objects_params
(
[id_object] int IDENTITY(1,1) not NUll,
[id_param] smallint NUll,
[cdate] smalldatetime not NUll,
[value] float not NUll
)ON [PRIMARY]

ALTER TABLE objects_params WITH CHECK ADD CONSTRAINT FK_Id_params FOREIGN KEY([Id_param])
REFERENCES Params ([Id_param])
GO

SET IDENTITY_INSERT objects_params ON
DECLARE @cnt INT = 0;
DECLARE @datetime smalldatetime = '2018-01-01 00:00:00';
DECLARE @id_object INT = 1;
WHILE @cnt < 50
BEGIN
INSERT objects_params ([id_object], [id_param],[cdate],[value])
values ( floor(13*RAND()+1),case when floor(4*RAND()+1)>3 then Null else floor(4*RAND()+1) end ,@datetime,cast(floor(1000*RAND()+1)*RAND() as decimal(8,2)) )
SET @cnt = @cnt + 1;
SET @datetime = DATEADD(day,cast(@datetime as int),1)
set @id_object=@id_object+1
END;

Нужно найти для всех товаров актуальные цены на заданный период дат. Актуальной ценой считается последняя полученная цена.

Написал запрос, но как сделать для произвольных скидок, без case?

SELECT [id_object]
,sum("Скидка Пенсионер") as "Скидка Пенсионер"
,sum("Скидка прекрасного дня") as "Скидка прекрасного дня"
,sum("Скидка Счастливчик") as "Скидка Счастливчик"
,sum("Скидка здравствуй") as "Скидка здравствуй"
FROM (
SELECT [id_object]
,case when name = 'Скидка Пенсионер' and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка Пенсионер"
,case when name = 'Скидка прекрасного дня' and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка прекрасного дня"
,case when name = 'Скидка Счастливчик'and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка Счастливчик"
,case when name = 'Скидка здравствуй' and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка здравствуй"
FROM objects_params as s
left JOIN Params as p on s.[id_param]=p.[id_param]
WHERE cast(s.cdate as date) between '2018-01-02' and '2018-01-05'
) as s
group by [id_object]

Пытался использовать динамический sql c pivot, но то что я пишу не работает

Помогите, пожалуйста!

Помогите, пожалуйста,
...
Рейтинг: 0 / 0
09.02.2018, 14:43
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39599743
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
FAQ: Выборка первой/последней записи в группах
...
Рейтинг: 0 / 0
09.02.2018, 15:28
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39599815
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Takashi,

а вы mysql от ms sql не отличаете?

Модератор: Тема перенесена из форума "MySQL".
...
Рейтинг: 0 / 0
09.02.2018, 16:44
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39600134
Дед-Папыхтет
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дед-Папыхтет
Участник
Takashi,

Pivot
...
Рейтинг: 0 / 0
11.02.2018, 08:10
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603766
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
Дед-ПапыхтетTakashi,

Pivot

как я понимаю pivot поможет если знаешь какие поля.
...
Рейтинг: 0 / 0
18.02.2018, 21:12
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603767
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
попробовал реализовать динамический запрос, но выдаёт ошибку
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DECLARE @sql varchar (8000)
DECLARE @id_param NVARCHAR(MAX)=N'1'
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+ '@id_param)'
DECLARE @data NVARCHAR(MAX)=N'2017-05-02'

SET @sql=
'SELECT [id_object]
		,sum("'+@name_param+'") as "'+@name_param+'"
FROM (
	SELECT [id_object]
			,case when name = '+ @name_param + ' '+ 'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value  else 0 end as "'+@name_param+'"
	FROM objects_params as s
	left JOIN Params as p on s.[id_param]=p.[id_param]
	WHERE cast(s.cdate as date) <='  + @data +
	') as s
group by [id_object]'

exec(@SQL)



Сообщение 137, уровень 15, состояние 2, строка 153
Необходимо объявить скалярную переменную "@id_param".
...
Рейтинг: 0 / 0
18.02.2018, 21:13
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603806
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Код: sql
1.
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')'

Однако, дело не только в этом.
В SUM() подзапрос вообще нельзя использовать.

Зачем везде натыканы двойные кавычки, если там надо писать пары одинарных?
Кстати, кое-где вообще пропущено. И у даты тоже.

Это я только про синтаксис, если что.
...
Рейтинг: 0 / 0
18.02.2018, 22:01
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603811
Дед-Папыхтет
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дед-Папыхтет
Участник
Takashi,

Добавь перед exec (@sql) команду print @sql
И посмотри что за строку сформировал. Отлаживать учись,
...
Рейтинг: 0 / 0
18.02.2018, 22:08
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603823
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
iap
Код: sql
1.
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')'

Однако, дело не только в этом.
В SUM() подзапрос вообще нельзя использовать.

Зачем везде натыканы двойные кавычки, если там надо писать пары одинарных?
Кстати, кое-где вообще пропущено. И у даты тоже.

Это я только про синтаксис, если что.

Как тогда сделать сумму по полю которое получается в подзапросе?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
DECLARE @sql varchar (8000)
DECLARE @id_param NVARCHAR(MAX)=N'1'
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')'
DECLARE @data NVARCHAR(MAX)=N'(2017-05-02)'

SET @sql=
'SELECT [id_object]
		,sum("Размер") as "'+@name_param+'"
FROM (
	SELECT [id_object]
			,case when name = '+ @name_param + ' '+ 'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value  else 0 end as '''+@name_param+'''
	FROM objects_params as s
	left JOIN Params as p on s.[id_param]=p.[id_param]
	WHERE cast(s.cdate as date) <='  + '''@data''' +
	') as s
group by [id_object]'

exec(@SQL)
...
Рейтинг: 0 / 0
18.02.2018, 22:24
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603826
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
Takashi,

получите это ваше name_param не как кусок динамики, а как результат запроса перед динамикой
Код: sql
1.
DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param)
...
Рейтинг: 0 / 0
18.02.2018, 22:31
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603832
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
ДедушкаTakashi,

получите это ваше name_param не как кусок динамики, а как результат запроса перед динамикой
Код: sql
1.
DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param)




спасибо! сделал

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
DECLARE @sql varchar (8000)
DECLARE @id_param NVARCHAR(MAX)=N'1'
DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param)
DECLARE @data NVARCHAR(MAX)=('2017-05-02')

SET @sql=
'SELECT [id_object]
		,sum("'+@name_param+'") as "'+@name_param+'"
FROM (
	SELECT [id_object]
			,case when name =  '''+@name_param+''' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value  else 0 end as "'+@name_param+'"
	FROM objects_params as s
	left JOIN Params as p on s.[id_param]=p.[id_param]
	WHERE cast(s.cdate as date) <=   '''+@data+''') as s
group by [id_object]'
exec(@SQL)
...
Рейтинг: 0 / 0
18.02.2018, 22:48
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603835
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
изначально я использовал запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT [id_object]
		,sum("Размер") as "Размер"
		,sum("Наличие фурнитуры") as "Наличие фурнитуры"
		,sum("Наличие петель") as "Наличие петель"
		,sum("Защита от влаги") as "Защита от влаги"
FROM (
	SELECT [id_object]
			,case when name = 'Размер'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value  else 0 end as "Размер"
			,case when name = 'Наличие фурнитуры' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value  else 0 end as "Наличие фурнитуры"
			,case when name = 'Наличие петель'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "Наличие петель"
			,case when name = 'Защита от влаги' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "Защита от влаги"
	FROM objects_params as s
	left JOIN Params as p on s.[id_param]=p.[id_param]
	WHERE cast(s.cdate as date) <= '2017-05-02' -- @data 
	) as s
group by [id_object];



решил прибегнуть к динамическому запросу, но в итоге в каждом case имя параметра указывать всё равно придется, или есть другое решение?


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
DECLARE @sql varchar (8000)
DECLARE @id_param NVARCHAR(MAX)=N'1'
DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param)
DECLARE @data NVARCHAR(MAX)=('2017-05-02')

SET @sql=
'SELECT [id_object]
		,sum("'+@name_param+'") as "'+@name_param+'"
FROM (
	SELECT [id_object]
			,case when name =  '''+@name_param+''' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value  else 0 end as "'+@name_param+'"
	FROM objects_params as s
	left JOIN Params as p on s.[id_param]=p.[id_param]
	WHERE cast(s.cdate as date) <=   '''+@data+''') as s
group by [id_object]'
exec(@SQL)
...
Рейтинг: 0 / 0
18.02.2018, 22:55
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39603853
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
Takashiкак я понимаю pivot поможет если знаешь какие поля.сначала получите нужный вам результат, а потом делайте динамик пивот
пример смотрите тут
...
Рейтинг: 0 / 0
18.02.2018, 23:23
| Ответить | Цитировать | Написать  
Нужно транспонировать таблицу
    #39604451
Takashi
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Takashi
Гость
Дедушка, спасибо за наглядный пример, удалось реализовать

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @data NVARCHAR(MAX)=('2017-05-02')

SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name)
  FROM (SELECT name FROM Params) AS x;
SET @sql = N'
SELECT id_object, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT id_object
		,p.[name]
		,case when s.cdate = MAX(s.cdate) over (partition by [id_object],s.[id_param] ) then value else 0 end as [max]
FROM objects_params as s
left JOIN Params as p on s.[id_param]=p.[id_param]
WHERE cast(s.cdate as date) <= '''+@data+'''
) AS j
PIVOT
(
  max([max]) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
...
Рейтинг: 0 / 0
19.02.2018, 21:28
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Нужно транспонировать таблицу
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]