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

Откуда:
Сообщений: 7
Здравствуйте!
На сервер собираются данные от приборов контроля в таблицу:
CREATE TABLE [dbo].[Main](
	[ID_CHANNEL] [smallint] NOT NULL, -- номер прибора
	[ID_RECDATE] [datetime2](0) NOT NULL, --дата регистрации измеренного значения
	[MESVALUE] [float] NOT NULL, -- измеренное значение.
 CONSTRAINT [PK_Main] PRIMARY KEY CLUSTERED


данные представляют из себя примерно следующее:
SELECT [ID_CHANNEL]
      ,[ID_RECDATE]
      ,[MESVALUE]
  FROM [KIPIADAQDATABASE].[dbo].[Main]
  where [ID_RECDATE] between '2014-06-03 07:00:00' AND '2014-06-03 07:01:00'
  ORDER BY [ID_RECDATE]

+ = таблица с данными
ID_CHANNELID_RECDATEMESVALUE
12014-06-03 07:00:02759
22014-06-03 07:00:02817
32014-06-03 07:00:02837
42014-06-03 07:00:02848
52014-06-03 07:00:02850
62014-06-03 07:00:02849
72014-06-03 07:00:02846
82014-06-03 07:00:02841
92014-06-03 07:00:02815
102014-06-03 07:00:02760
112014-06-03 07:00:02820
122014-06-03 07:00:02840
132014-06-03 07:00:02850
142014-06-03 07:00:02850
152014-06-03 07:00:02850
162014-06-03 07:00:02845
172014-06-03 07:00:02840
182014-06-03 07:00:02815
192014-06-03 07:00:0261.8
202014-06-03 07:00:0254.8
212014-06-03 07:00:0291.6
222014-06-03 07:00:0264.3
232014-06-03 07:00:0254.1
242014-06-03 07:00:0274.7
252014-06-03 07:00:0222.2
262014-06-03 07:00:0226.2
272014-06-03 07:00:0266.8
282014-06-03 07:00:02489
292014-06-03 07:00:023.65
302014-06-03 07:00:0255.3
312014-06-03 07:00:0230
322014-06-03 07:00:021010
332014-06-03 07:00:02828.2
342014-06-03 07:00:02839.8
352014-06-03 07:00:02821.3
352014-06-03 07:00:12821.1
342014-06-03 07:00:12839.8
332014-06-03 07:00:12829.9
322014-06-03 07:00:121010
312014-06-03 07:00:1230
302014-06-03 07:00:1255.3
292014-06-03 07:00:123.65
282014-06-03 07:00:12489
272014-06-03 07:00:1256.8
262014-06-03 07:00:1226
252014-06-03 07:00:1222.2
242014-06-03 07:00:1275.2
232014-06-03 07:00:1254.1
222014-06-03 07:00:1264.7
212014-06-03 07:00:1274.5
202014-06-03 07:00:1235.8
192014-06-03 07:00:1262.2
182014-06-03 07:00:12815
172014-06-03 07:00:12840
162014-06-03 07:00:12845
152014-06-03 07:00:12850
142014-06-03 07:00:12850
132014-06-03 07:00:12850
122014-06-03 07:00:12840
112014-06-03 07:00:12820
102014-06-03 07:00:12760
92014-06-03 07:00:12815
82014-06-03 07:00:12841
72014-06-03 07:00:12846
62014-06-03 07:00:12849
52014-06-03 07:00:12850
42014-06-03 07:00:12848
32014-06-03 07:00:12838
22014-06-03 07:00:12819
12014-06-03 07:00:12759
12014-06-03 07:00:22760
22014-06-03 07:00:22820
32014-06-03 07:00:22839
42014-06-03 07:00:22848
52014-06-03 07:00:22850
62014-06-03 07:00:22849
72014-06-03 07:00:22846
82014-06-03 07:00:22841
92014-06-03 07:00:22815
102014-06-03 07:00:22760
112014-06-03 07:00:22820
122014-06-03 07:00:22840
132014-06-03 07:00:22850
142014-06-03 07:00:22850
152014-06-03 07:00:22850
162014-06-03 07:00:22845
172014-06-03 07:00:22840
182014-06-03 07:00:22815
192014-06-03 07:00:2262.7
202014-06-03 07:00:2236
212014-06-03 07:00:2256.2
222014-06-03 07:00:2265.2
232014-06-03 07:00:2254.1
242014-06-03 07:00:2275.6
252014-06-03 07:00:2222.2
262014-06-03 07:00:2225.8
272014-06-03 07:00:2256.8
282014-06-03 07:00:22489
292014-06-03 07:00:223.65
302014-06-03 07:00:2255.3
312014-06-03 07:00:2230
322014-06-03 07:00:221010
332014-06-03 07:00:22828.7
342014-06-03 07:00:22839.7
352014-06-03 07:00:22821.2
352014-06-03 07:00:32820
342014-06-03 07:00:32839.8
332014-06-03 07:00:32826.6
322014-06-03 07:00:321010
312014-06-03 07:00:3230
302014-06-03 07:00:3255.3
292014-06-03 07:00:323.65
282014-06-03 07:00:32489
272014-06-03 07:00:3256.8
262014-06-03 07:00:3225.6
252014-06-03 07:00:3222.2
242014-06-03 07:00:3276
232014-06-03 07:00:3254.1
222014-06-03 07:00:3265.6
212014-06-03 07:00:3216.1
202014-06-03 07:00:3225.7
192014-06-03 07:00:3242.7
182014-06-03 07:00:32815
172014-06-03 07:00:32840
162014-06-03 07:00:32845
152014-06-03 07:00:32850
142014-06-03 07:00:32850
132014-06-03 07:00:32850
122014-06-03 07:00:32840
112014-06-03 07:00:32820
102014-06-03 07:00:32760
92014-06-03 07:00:32815
82014-06-03 07:00:32841
72014-06-03 07:00:32846
62014-06-03 07:00:32849
52014-06-03 07:00:32850
42014-06-03 07:00:32848
32014-06-03 07:00:32842
22014-06-03 07:00:32821
12014-06-03 07:00:32760
12014-06-03 07:00:42760
22014-06-03 07:00:42821
32014-06-03 07:00:42843
42014-06-03 07:00:42849
52014-06-03 07:00:42850
62014-06-03 07:00:42849
72014-06-03 07:00:42846
82014-06-03 07:00:42841
92014-06-03 07:00:42815
102014-06-03 07:00:42760
112014-06-03 07:00:42820
122014-06-03 07:00:42840
132014-06-03 07:00:42850
142014-06-03 07:00:42850
152014-06-03 07:00:42850
162014-06-03 07:00:42845
172014-06-03 07:00:42840
182014-06-03 07:00:42815
192014-06-03 07:00:4242.7
202014-06-03 07:00:4225.2
212014-06-03 07:00:4210
222014-06-03 07:00:4255.9
232014-06-03 07:00:4254.1
242014-06-03 07:00:4276.5
252014-06-03 07:00:4222.2
262014-06-03 07:00:4225.4
272014-06-03 07:00:4256.8
282014-06-03 07:00:42489
292014-06-03 07:00:423.65
302014-06-03 07:00:4255.3
312014-06-03 07:00:4230
322014-06-03 07:00:421010
332014-06-03 07:00:42825.3
342014-06-03 07:00:42839.8
352014-06-03 07:00:42819.2
352014-06-03 07:00:52817.3
342014-06-03 07:00:52839.5
332014-06-03 07:00:52822.5
322014-06-03 07:00:521010
312014-06-03 07:00:5230
302014-06-03 07:00:5255.3
292014-06-03 07:00:523.65
282014-06-03 07:00:52489
272014-06-03 07:00:5256.8
262014-06-03 07:00:5225.2
252014-06-03 07:00:5222.2
242014-06-03 07:00:5276.9
232014-06-03 07:00:5254.1
222014-06-03 07:00:5216.1
212014-06-03 07:00:5210
202014-06-03 07:00:5224.7
192014-06-03 07:00:5242.7
182014-06-03 07:00:52815
172014-06-03 07:00:52840
162014-06-03 07:00:52845
152014-06-03 07:00:52850
142014-06-03 07:00:52850
132014-06-03 07:00:52850
122014-06-03 07:00:52840
112014-06-03 07:00:52820
102014-06-03 07:00:52760
92014-06-03 07:00:52815
82014-06-03 07:00:52841
72014-06-03 07:00:52846
62014-06-03 07:00:52849
52014-06-03 07:00:52850
42014-06-03 07:00:52850
32014-06-03 07:00:52844
22014-06-03 07:00:52821
12014-06-03 07:00:52760


нужно вытаскивать данные в клиент (написан на C#) в формате, удобном для представления в datagridviewer для оператора
т.е.
ID_RECDATEchan1chan2chan3chan4
07:00:02759817837848
07:00:12760820839848

и так далее
если по какому то каналу поле пустое в заданное время пишем NULL

у меня нет идей кроме как SELECT нужных каналов и UNION, но как то надо эти повторяющиеся NULL убрать
+ = предполагаемый запрос
SELECT [ID_RECDATE], [MESVALUE] AS chan1, null AS chan2, null as chan3, null as chan4
  FROM [KIPIADAQDATABASE].[dbo].[Main]
  where [ID_CHANNEL] = 1 AND [ID_RECDATE] between '2014-06-03 07:00:00' AND '2014-06-03 07:01:00'
  union
  SELECT [ID_RECDATE],  null AS chan1, [MESVALUE] AS chan2, null as chan3, null as chan4
  FROM [KIPIADAQDATABASE].[dbo].[Main]
  where [ID_CHANNEL] = 2 AND [ID_RECDATE] between '2014-06-03 07:00:00' AND '2014-06-03 07:01:00'
 union
 SELECT [ID_RECDATE], null  AS chan1, null AS chan2, [MESVALUE] as chan3, null as chan4
  FROM [KIPIADAQDATABASE].[dbo].[Main]
  where [ID_CHANNEL] = 3 AND [ID_RECDATE] between '2014-06-03 07:00:00' AND '2014-06-03 07:01:00'
 union
 SELECT [ID_RECDATE], null AS chan1, null AS chan2, null as chan3, [MESVALUE] as chan4
  FROM [KIPIADAQDATABASE].[dbo].[Main]
  where [ID_CHANNEL] = 4 AND [ID_RECDATE] between '2014-06-03 07:00:00' AND '2014-06-03 07:01:00'

  ORDER BY [ID_RECDATE]

Получим
+ результат
ID_RECDATEchan1chan2chan3chan4
2014-06-03 07:00:02NULLNULLNULL848
2014-06-03 07:00:02NULLNULL837NULL
2014-06-03 07:00:02NULL817NULLNULL
2014-06-03 07:00:02759NULLNULLNULL
2014-06-03 07:00:12NULLNULLNULL848
2014-06-03 07:00:12NULLNULL838NULL
2014-06-03 07:00:12NULL819NULLNULL
2014-06-03 07:00:12759NULLNULLNULL
2014-06-03 07:00:22NULLNULLNULL848
2014-06-03 07:00:22NULLNULL839NULL
2014-06-03 07:00:22NULL820NULLNULL
2014-06-03 07:00:22760NULLNULLNULL
2014-06-03 07:00:32NULLNULLNULL848
2014-06-03 07:00:32NULLNULL842NULL
2014-06-03 07:00:32NULL821NULLNULL
2014-06-03 07:00:32760NULLNULLNULL
2014-06-03 07:00:42NULLNULLNULL849
2014-06-03 07:00:42NULLNULL843NULL
2014-06-03 07:00:42NULL821NULLNULL
2014-06-03 07:00:42760NULLNULLNULL
2014-06-03 07:00:52NULLNULLNULL850
2014-06-03 07:00:52NULLNULL844NULL
2014-06-03 07:00:52NULL821NULLNULL
2014-06-03 07:00:52760NULLNULLNULL

Подскажите, может кто сталкивался с разворачиванием таблицы. Как это делают профессионалы?
3 июн 14, 10:16    [16110571]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
PIVOT ?
3 июн 14, 10:18    [16110577]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
_human
Member

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

для pivot-а нужна какая-то агрегация..
select p.[ID_RECDATE], p.[1], [2], [3], [4]
from
(
	select [ID_CHANNEL]
		  ,[ID_RECDATE]
		  ,[MESVALUE]
	from [dbo].[Main]
) s
pivot (sum(s.[MESVALUE]) for s.[ID_CHANNEL] in ([1], [2], [3], [4])) as p


если просто разворот, то нужно обеспечить группировку по уникальным значениям(row_number) + любая ф-ия SUM,MIN,MAX кроме COUNT.
3 июн 14, 10:23    [16110612]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
dpzr
Member

Откуда:
Сообщений: 7
Спасибо большое!

Если не трудно поясните, зачем нам функция sum? как суммируется поле при развороте таблицы?
3 июн 14, 10:56    [16110880]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
dpzr
зачем нам функция sum?

Потому что PIVOT требует агрегатную функцию

dpzr
как суммируется поле при развороте таблицы?

Агрегатных функций существуеьт больше одной
3 июн 14, 11:02    [16110945]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
dpzr
Если не трудно поясните, зачем нам функция sum? как суммируется поле при развороте таблицы?
Предположительно ничто не ограничивает чтобы в таблице было более одной строки на каждую комбинацию.
Sum - может быть _human это использует как подстраховку, чтобы значения никуда не пропали.

Но в общем виде Sum не подходит:
SELECT	P.*
FROM	(VALUES (1,'A'),(1,'B'))X(ID,Value)
PIVOT	(Sum(Value) FOR ID IN ([1],[2])) P
Он работает только для числовых типов. А вот к примеру Max/Min работает для любых.
Обычно пишут Max, но на самом деле не важно, ибо NULL значения агрегирующими функциями игнорируются.

PIVOT это сахар. Т.е. это не элемент языка, а надстройка. Она переводится (кстати порой не очень удачно) в GROUP BY + CASE
SELECT	ID_RECDATE
,	Max(CASE WHEN ID_CHANNEL = 1 THEN MesValue END)	AS [1]
,	Max(CASE WHEN ID_CHANNEL = 2 THEN MesValue END)	AS [2]
,	Max(CASE WHEN ID_CHANNEL = 3 THEN MesValue END)	AS [3]
FROM	dbo.Main	T
GROUP BY ID_RECDATE


dpzr
нужно вытаскивать данные в клиент (написан на C#) в формате, удобном для представления в datagridviewer для оператора
Советую такие преобразования делать только на клиенте.
Скуль для обработки данных, а не для их представления.
В C# вообще нет никаких проблем с представлениями и разворотами.
3 июн 14, 11:09    [16111033]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Mnior
dpzr
нужно вытаскивать данные в клиент (написан на C#) в формате, удобном для представления в datagridviewer для оператора
Советую такие преобразования делать только на клиенте.
Скуль для обработки данных, а не для их представления.
В C# вообще нет никаких проблем с представлениями и разворотами.
Притом способов просто куча.
Можно просто при считывании (отсортивованных) данных из DataReader записывать по соответствующим колонкам. Быстрый и топорный вариант.
А можно использовать куча готовых функций которые это делают с уже заполненными DataTable.
А также можно использовать PIVOT представления, которые с форматом хранения ничего и не делают. Тяжёлый в настройке, удобный для дальнейшей обработки данных (если их надо не просто отобразить ни и менять/сохранять).

Не зажимайте себе моск вариантами реализации.

Ещё раз повторю. SQL для обработки данных, а не для их представления.

Это я ещё не говорю, что сервер один, а клиентов много, перекладывать "расчёты" с множества клиентов, на один бедный сервер тупо не эффективно. Это если вы не понимаете парадигму разделения (не смешивания) функционала/ответственности.
3 июн 14, 11:19    [16111128]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
dpzr
Member

Откуда:
Сообщений: 7
Mnior

На клиента придётся сначала вытаскивать данные в dataset запросом как есть, а потом их обрабатывать копированием в какую-нибудь коллекцию или table. Получиться что отъедаем память два раза на одни и те же данные.

Так что пусть пыхтит сервер.
3 июн 14, 11:24    [16111160]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
dpzr
Member

Откуда:
Сообщений: 7
Mnior
Mnior
пропущено...
Советую такие преобразования делать только на клиенте.
Скуль для обработки данных, а не для их представления.
В C# вообще нет никаких проблем с представлениями и разворотами.
Притом способов просто куча.
Можно просто при считывании (отсортивованных) данных из DataReader записывать по соответствующим колонкам. Быстрый и топорный вариант.
А можно использовать куча готовых функций которые это делают с уже заполненными DataTable.
А также можно использовать PIVOT представления, которые с форматом хранения ничего и не делают. Тяжёлый в настройке, удобный для дальнейшей обработки данных (если их надо не просто отобразить ни и менять/сохранять).

Не зажимайте себе моск вариантами реализации.

Ещё раз повторю. SQL для обработки данных, а не для их представления.

Это я ещё не говорю, что сервер один, а клиентов много, перекладывать "расчёты" с множества клиентов, на один бедный сервер тупо не эффективно. Это если вы не понимаете парадигму разделения (не смешивания) функционала/ответственности.


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

Спасибо всем за помощь!
3 июн 14, 11:27    [16111176]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
dpzr
На клиента придётся сначала вытаскивать данные в dataset запросом как есть
Что за бред, я же писал:
Mnior
Можно просто при считывании (отсортивованных) данных из DataReader записывать по соответствующим колонкам.
Слепой чтоле?

То что у тебя туго с C# (и не только), не доказывает что так делать неправильно.
3 июн 14, 16:12    [16113822]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8488
dpzr
Mnior

На клиента придётся сначала вытаскивать данные в dataset запросом как есть, а потом их обрабатывать копированием в какую-нибудь коллекцию или table. Получиться что отъедаем память два раза на одни и те же данные.

Так что пусть пыхтит сервер.


Нет, не так. C# кроме ADO Dataset имеет и механизм DataReader, который позволяет построчно считывать результат и на лету помещать значения колонок куда требуется. Потренируйте С# :)
3 июн 14, 16:41    [16114176]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
dpzr
Member

Откуда:
Сообщений: 7
Ясно. Приму к сведению и исправлюсь.
3 июн 14, 18:45    [16115015]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
dpzr
Member

Откуда:
Сообщений: 7
Да. датаридер это хорошо.
+
В процессе чтения в каждый момент времени в оперативной памяти клиентского компьютера находится одна-единственная текущая запись. Поэтому DataReader требует очень мало памяти клиента, даже если общий объем выборки данных велик.
Конечно же, за подобную экономию всегда приходится чем-то расплачиваться. В данном случае ценой является постоянно открытое соединение с источником данных, что создает нагрузку на сеть, а также требует от сервера баз данных выделения некоторых ресурсов на поддержку соединения с клиентом (если источником данных является SQL-сервер).club.shelek.ru/viewart.php?id=216 msdn.microsoft.com/ru-ru/library/ms254931%28v=vs.110%29.aspx
3 июн 14, 19:15    [16115129]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на разворот таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
dpzr,

Советую вам не цитировать. Ибо вы не разбираетесь, а тыкать разбирающимся слова других скорее будет нелепо.
Вы не знаете как работает Fill(DataSet) внутрях.

Гуманитарии, блин.
3 июн 14, 20:56    [16115506]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить