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

Откуда:
Сообщений: 9
 		Добрый день!

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

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, но то что я пишу не работает

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

Помогите, пожалуйста,
9 фев 18, 14:43    [21180210]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21494
 		FAQ: Выборка первой/последней записи в группах
9 фев 18, 15:28    [21180401]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1391
 		Takashi,

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

Модератор: Тема перенесена из форума "MySQL".


Сообщение было отредактировано: 9 фев 18, 16:56
9 фев 18, 16:44    [21180764]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Дед-Папыхтет
Member [заблокирован]

Откуда:
Сообщений: 2808
 		Takashi,

Pivot
11 фев 18, 08:10    [21182793]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Takashi
Member

Откуда:
Сообщений: 9
Дед-Папыхтет
Takashi,

Pivot


как я понимаю pivot поможет если знаешь какие поля.
18 фев 18, 21:12    [21200268]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Takashi
Member

Откуда:
Сообщений: 9
 		попробовал реализовать динамический запрос, но выдаёт ошибку 
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".
18 фев 18, 21:13    [21200269]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
 
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')'
Однако, дело не только в этом.
В SUM() подзапрос вообще нельзя использовать.

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

Это я только про синтаксис, если что.
18 фев 18, 22:01    [21200353]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Дед-Папыхтет
Member [заблокирован]

Откуда:
Сообщений: 2808
 		Takashi,

Добавь перед exec (@sql) команду print @sql
И посмотри что за строку сформировал. Отлаживать учись,
18 фев 18, 22:08    [21200362]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Takashi
Member

Откуда:
Сообщений: 9
iap
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')'
Однако, дело не только в этом.
В SUM() подзапрос вообще нельзя использовать.

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

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


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

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)
18 фев 18, 22:24    [21200387]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5145
 		Takashi,

получите это ваше name_param не как кусок динамики, а как результат запроса перед динамикой
DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param)
18 фев 18, 22:31    [21200395]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Takashi
Member

Откуда:
Сообщений: 9
Дедушка
Takashi,

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



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

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)
18 фев 18, 22:48    [21200413]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Takashi
Member

Откуда:
Сообщений: 9
 		изначально я использовал запрос 
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 имя параметра указывать всё равно придется, или есть другое решение?


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)
18 фев 18, 22:55    [21200421]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5145
Takashi
как я понимаю pivot поможет если знаешь какие поля.
сначала получите нужный вам результат, а потом делайте динамик пивот
пример смотрите тут
18 фев 18, 23:23    [21200458]     Ответить | Цитировать Сообщить модератору
 Re: Нужно транспонировать таблицу  [new]
Takashi
Member

Откуда:
Сообщений: 9
 		Дедушка, спасибо за наглядный пример, удалось реализовать

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;
19 фев 18, 21:28    [21203260]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить