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

Откуда: оттуда
Сообщений: 26
Есть данные такого вида

device_parameter_id device_id data date_time

Соответственно данные в столбцах стремятся к повторяемости. Требуется в результате запроса "избавиться" от столбца device_parameter_id и data вместо них должны быть 3 столбца: dpid1, dpid2, dpid3, объединять же строки нужно по device_id и date_time

Например изначальная таблица
device_parameter_id device_id data date_time
1 1 100 yestoday
2 1 101 yestoday
3 1 102 yestoday
1 2 103 yestoday
2 2 104 yestoday
3 2 105 yestoday
1 1 106 today
2 1 107 today
3 1 108 today
1 2 106 today
2 2 107 today
3 2 108 today

Надо получить
date_time device_iddpid1 dpid2 dpid3
yestoday 1 100 101 102
yestoday 2 103 104 105
today 1 106 107 108
today 2 106 107 108
10 фев 12, 15:04    [12069860]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Red2
Есть данные такого вида

device_parameter_id device_id data date_time

Соответственно данные в столбцах стремятся к повторяемости. Требуется в результате запроса "избавиться" от столбца device_parameter_id и data вместо них должны быть 3 столбца: dpid1, dpid2, dpid3, объединять же строки нужно по device_id и date_time

Например изначальная таблица
device_parameter_id device_id data date_time
1 1 100 yestoday
2 1 101 yestoday
3 1 102 yestoday
1 2 103 yestoday
2 2 104 yestoday
3 2 105 yestoday
1 1 106 today
2 1 107 today
3 1 108 today
1 2 106 today
2 2 107 today
3 2 108 today

Надо получить
date_time device_iddpid1 dpid2 dpid3
yestoday 1 100 101 102
yestoday 2 103 104 105
today 1 106 107 108
today 2 106 107 108

Например так:
declare @t table (device_parameter_id int, device_id int, data int, date_time varchar(10));
insert into @t values
(1, 1, 100, 'yestoday'),
(2, 1, 101, 'yestoday'),
(3, 1, 102, 'yestoday'),
(1, 2, 103, 'yestoday'),
(2, 2, 104, 'yestoday'),
(3, 2, 105, 'yestoday'),
(1, 1, 106, 'today'),
(2, 1, 107, 'today'),
(3, 1, 108, 'today'),
(1, 2, 106, 'today'),
(2, 2, 107, 'today'),
(3, 2, 108, 'today')

select
	date_time,
	device_id,
	ppid1 = sum(case when device_parameter_id = 1 then data end),
	ppid2 = sum(case when device_parameter_id = 2 then data end),
	ppid3 = sum(case when device_parameter_id = 3 then data end)
from @t
group by
	date_time,
	device_id
order by
	date_time desc	
10 фев 12, 16:02    [12070471]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
Red2
Member

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

Спасибо, только меня смущает
автор
sum()
10 фев 12, 16:46    [12070946]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
Red2
Member

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

и почему то ругается:

sql
1 Недопустимое имя столбца "device_parameter_id". SQL2.sql 4 39
10 фев 12, 16:54    [12071017]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
underalex
Member

Откуда: Киев
Сообщений: 50
Red2,

PIVOT?
10 фев 12, 16:58    [12071056]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
Red2
Member

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

Но тут всеравно надо будет делать 3 селекта?
13 фев 12, 10:19    [12079755]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Red2
Спасибо, только меня смущает
автор
sum()

Не нравится sum, используйте другую.
Red2
и почему то ругается:
sql
1 Недопустимое имя столбца "device_parameter_id". SQL2.sql 4 39

Какая версия сервера у вас? Если скопипастить приведенный выше запрос и выполнить его на 2008 и выше - не ругается. Так что, судя по тексту ошибки, она является следствием ваших попыток адаптировать запрос для своих таблиц, ищите где налажали.
Red2
Но тут всеравно надо будет делать 3 селекта?
Там все равно нужно будет делать агрегацию.
13 фев 12, 12:41    [12080822]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
К сожалению pivot в лоб использовать не получается, т.к. поле [data] бинарное:
CREATE TABLE astral_db.dbo.flowmeter_data (
  id bigint IDENTITY,
  flowmeter_parameter_id bigint NOT NULL,
  device_id bigint NOT NULL,
  [data] varbinary(100) NOT NULL,
  date_time datetime NOT NULL,
  CONSTRAINT PK_flowmeter_data PRIMARY KEY (id),
  CONSTRAINT FK_flowmeter_data_device FOREIGN KEY (device_id) REFERENCES dbo.device (id),
  CONSTRAINT FK_flowmeter_data_flowmeter_parameter FOREIGN KEY (flowmeter_parameter_id) REFERENCES dbo.flowmeter_parameter (id)
)
GO

ALTER AUTHORIZATION ON dbo.flowmeter_data TO dbo


а на такое ругается
SELECT top 10 [date_time], sys.fn_sqlvarbasetostr( [data] ) as [hdata], [3], [4]
FROM (SELECT id, device_id,date_time,sys.fn_sqlvarbasetostr([data]) as [hdata], flowmeter_parameter_id FROM astral_db.dbo.flowmeter_data) AS SourceTable
PIVOT
( 
max (hdata)
FOR flowmeter_parameter_id 
IN ( [3], [4])
) AS PivotTable;

1 Недопустимое имя столбца "data". C:\Users\HP\Documents\project\allcom\pivot.sql 1 51
2 Курсор не объявлен. C:\Users\HP\Documents\project\allcom\pivot.sql 1 52
14 фев 12, 00:28    [12085658]     Ответить | Цитировать Сообщить модератору
 Re: Как преобразовать данные из одного столбца в несколько дополнительных?  [new]
Red2
Member

Откуда: оттуда
Сообщений: 26
Получилось:
SELECT top 20 [date_time], device_id, [1], [2] , [3], [5]
FROM (SELECT [date_time], device_id, flowmeter_parameter_id,sys.fn_sqlvarbasetostr( [data] ) as [hdata] FROM astral_db.dbo.flowmeter_data) AS SourceTable
PIVOT
( 
max ([hdata])
FOR flowmeter_parameter_id IN ([1], [2], [3], [5])
) AS PivotTable WHERE device_id=44 order by date_time desc;
15 фев 12, 15:15    [12096266]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить