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

Откуда: Недалекое заМКАДье
Сообщений: 355
Сразу оговорюсь, с БД работаю нечасто и поэтому слаб в T-SQL. Решение смог сделать только с помощью c#. Но предполагаю ,что можно и на стороне сервера сделать.
Есть 2 таблицы: Значения и Параметры.
Структура "Значений":
ID Значения,
ID Испытания,
COUNTER, // счетчик номера измерения в испытании
ID Параметра,
Значение,
Флаг Недостоверности

Структура "Параметров":
ID Параметра
Название параметра.

Простым запросом select можно получить значения только для 1 параметра:
SELECT     TOP (100) PERCENT dbo.Значения.[Значение параметра]
FROM         dbo.Значения INNER JOIN
                      dbo.Параметры ON dbo.Значения.[ID Параметра] = dbo.Параметры.[ID Параметра]
WHERE     (dbo.Значения.[ID Испытания] = 21) AND (dbo.Параметры.[Название параметра] = N'DK151')
ORDER BY dbo.Значения.COUNTER
Т.е. такое:
Значение параметра
_____
400
401
400
...
Мне же надо получить в итоге результат вида:

DK151 | DK999 | DK666
____________________________
400 | 600 | 500
401 | 600 | 500
400 | 600 | 500
...

На шарпе сделал последовательные отдельные запросы по 1 параметру и добавление к таблице по 1 столбцу.
Но как это сделать в MSSQL? С помощью временной таблицы?


Правило сроков выполнения проекта (90 на 90). Первые 90% работы отнимают 10% времени, а последние 10% - оставшиеся 90% времени.
28 май 09, 15:30    [7240327]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
посмотрите на pivot в faq есть
28 май 09, 15:31    [7240339]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
Konst_One
посмотрите на pivot в faq есть

А тут как не крути - всё равно переменное количество столбцов. Если не названия параметров, то количество значений для них...
28 май 09, 15:35    [7240364]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
значит придется сначала привести данные к другому виду (временная таблица , например)
28 май 09, 15:38    [7240383]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
Konst_One, тогда подскажите хотя бы как во временной таблице колонки создавать и кидать туда значения в цикле :(
28 май 09, 15:45    [7240437]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
зачем колонки добавлять? создавайте сразу нужную вам структуру таблицы.

select [Название параметра] FROM dbo.[Параметры]
- это ваши названия колонок
28 май 09, 15:49    [7240469]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
Konst_One
зачем колонки добавлять? создавайте сразу нужную вам структуру таблицы.

select [Название параметра] FROM dbo.[Параметры]
- это ваши названия колонок

да... это мысль..
1. получить список такой
2. создать таблицу временную3
3. создать в ней столбцы соответственно этому списку
4. но как в эту таблицу потом писать значения для параметров? тут я не понимаю немного..
28 май 09, 15:55    [7240530]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
в этой табличке, как я понял, должно быть еще поле [ID Испытания], вот по нему всё и повяжете
28 май 09, 15:58    [7240543]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
Konst_One, я не про это.. просто же INSERT не сделать, потому что я сначала заполню первый столбец, потом второй и т.д. как тогда добавлять строки и писать значения
28 май 09, 16:12    [7240693]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
команда update есть еще , кромe insert
28 май 09, 16:13    [7240701]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
SELECT 
	V.[ID Испытания],
	V.COUNTER, 
	V.[Значение параметра],
	V.[ID Параметра]
FROM   dbo.[Значения] V
ORDER BY V.[ID Испытания], V.COUNTER

посмотрите сами.
может, что в голову придет =)
28 май 09, 16:18    [7240735]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
Т.е. для первого запроса делать INSERT, а потом UPDATE?... Уж очень не рационально получается.. Может всё-таки есть более простой способ?

Правило сроков выполнения проекта (90 на 90). Первые 90% работы отнимают
10% времени, а последние 10% - оставшиеся 90% времени.
28 май 09, 22:22    [7242028]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
iljy
Guest
akf,
вроде работает
declare @PNames as varchar(1024)
set @PNames = ''
select @PNames = @PNames + ',' +  [Название параметра] from dbo.Параметры
order by [ID Параметра]
set @PNames = substring(@PNames, 2, len(@PNames))

declare @Sql varchar(8000)
set @Sql = 
'SELECT COUNTER,' + @PNames +
' FROM' +
' (SELECT COUNTER, [Название параметра], [Значение]' +
' FROM '+
'	dbo.Значения t1 join dbo.Параметры t2 on t1.[ID Параметра] = t2.[ID Параметра]'+
' ) p'+
' PIVOT ('+
'	max([Значение])'+
'	FOR [Название параметра] IN	(' + @PNames + ')'+
' ) AS pvt order by COUNTER'

EXEC(@SQL)
29 май 09, 00:18    [7242227]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
iljy
Guest
akf,

можно добавить фильтр по испытаниям, а можно вывести данные сразу по всем испытаниям, разберетесь?
29 май 09, 00:32    [7242250]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
iljy
akf,

можно добавить фильтр по испытаниям, а можно вывести данные сразу по всем испытаниям, разберетесь?

спасибо! завтра на работе на боевой базе попробую мозг поломать!
29 май 09, 01:08    [7242291]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Кудряшка
Member

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

Может сначала на тестовой базе попробовать? :)
29 май 09, 04:13    [7242351]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
iljy
Guest
akf
iljy
akf,

можно добавить фильтр по испытаниям, а можно вывести данные сразу по всем испытаниям, разберетесь?

спасибо! завтра на работе на боевой базе попробую мозг поломать!


Не надо ломать, пригодится:)
set @Sql = 
'SELECT [ID Испытания], COUNTER,' + @PNames +
' FROM' +
' (SELECT [ID Испытания], COUNTER, [Название параметра], [Значение]' +
' FROM '+
'	dbo.Значения t1 join dbo.Параметры t2 on t1.[ID Параметра] = t2.[ID Параметра]'+
' WHERE ID Испытания = [Нужный номер]' + --эта строка - если нужно одно испытание.
                                                            -- В этом случает столбец ID Испытания можно из                                                         -- выборки убрать
' ) p'+
' PIVOT ('+
'	max([Значение])'+
'	FOR [Название параметра] IN	(' + @PNames + ')'+
' ) AS pvt order by [ID Испытания], COUNTER'
29 май 09, 09:30    [7242619]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
iljy,

declare @PNames as varchar(1024)
set @PNames = ''
select @PNames = @PNames + ',' +  [Название параметра] from dbo.[Параметры] order by [ID Параметра]
set @PNames = substring(@PNames, 2, len(@PNames))

declare @Sql varchar(8000)
set @Sql = 
'SELECT [ID Испытания], COUNTER,' + @PNames +
' FROM' +
' (SELECT [ID Испытания], COUNTER, [Название параметра], [Значение]' +
' FROM '+
'	dbo.[Значения] t1 join dbo.[Параметры] t2 on t1.[ID Параметра] = t2.[ID Параметра]'+
' WHERE [ID Испытания] = 21' + 
' ) p'+
' PIVOT ('+
'	max([Значение])'+
'	FOR [Название параметра] IN	(' + @PNames + ')'+
' ) AS pvt order by [ID Испытания], COUNTER'

EXEC(@SQL)

Ругается:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'p'.
29 май 09, 12:37    [7243708]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
у вас 2000 сервер ?
тогда еще раз повторю, что стоит статейку в FAQ почитать
29 май 09, 12:43    [7243741]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
Konst_One, нет, у меня 2005, а есть разница?
29 май 09, 12:50    [7243797]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
тогда внимательно смотрите статью в BOL про PIVOT
29 май 09, 12:54    [7243816]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
iljy
Guest
akf
iljy,

Ругается:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'p'.



create table [Значения]
(
[ID Значения] int identity(1,1),
[ID Испытания] int,
COUNTER int, 
[ID Параметра] int,
[Значение] int,
[Флаг Недостоверности] bit
)

//Структура "Параметров":
create table [Параметры]
(
[ID Параметра] int identity(1,1),
[Название параметра] varchar(20)
)


так объявлены таблицы

[ID Значения] [ID Испытания] COUNTER [ID Параметра] [Значение] [Флаг Недостоверности]
1 1 1 1 2 False
2 1 1 2 3 True
3 1 1 3 5 True
4 1 2 1 4 NULL
5 1 2 2 45 NULL
6 1 3 3 12 NULL

[ID Параметра] [Название параметра]
1 Param1
2 Param2
3 Param3

такие в них данные.
declare @PNames as varchar(1024)
set @PNames = ''
select @PNames = @PNames + ',' +  [Название параметра] from dbo.[Параметры] order by [ID Параметра]
set @PNames = substring(@PNames, 2, len(@PNames))

declare @Sql varchar(8000)
set @Sql = 
'SELECT [ID Испытания], COUNTER,' + @PNames +
' FROM' +
' (SELECT [ID Испытания], COUNTER, [Название параметра], [Значение]' +
' FROM '+
'	dbo.[Значения] t1 join dbo.[Параметры] t2 on t1.[ID Параметра] = t2.[ID Параметра]'+
' WHERE [ID Испытания] = 1' + 
' ) p'+
' PIVOT ('+
'	max([Значение])'+
'	FOR [Название параметра] IN	(' + @PNames + ')'+
' ) AS pvt order by [ID Испытания], COUNTER'

EXEC(@SQL)

Выполняется без ошибок.


1 1 2 3 5
1 2 4 45 NULL
1 3 NULL NULL 12

у вас скуль часом не 2000? В нем если что - PIVOT нету;) надо хитрее писать, но тоже можно.
29 май 09, 12:57    [7243833]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5637
ну так вы сделайте вместо exec(sql) print (sql) и гляньте что за текст получился. и ищите там ошибку.

для спящего время бодрствования равносильно сну
29 май 09, 12:58    [7243844]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
iljy
Guest
declare @PNames as varchar(8000)
set @PNames = ''
select @PNames = @PNames + ',' +
	'max(case when [Название параметра] = ''' + [Название параметра] +
    ''' then [Значение] else null end) as ''' + [Название параметра] + ''''
from dbo.[Параметры] order by [ID Параметра]
set @PNames = substring(@PNames, 2, len(@PNames))
select @PNames

declare @Sql varchar(8000)
set @Sql =
' SELECT [ID Испытания], COUNTER,' + @PNames +
' FROM' +
' (SELECT [ID Испытания], COUNTER, [Название параметра], [Значение]'+
' FROM' +
'	dbo.[Значения] t1 join dbo.[Параметры] t2 on t1.[ID Параметра] = t2.[ID Параметра]'+
' WHERE [ID Испытания] = 1' +
' ) p group by [ID Испытания], COUNTER'

EXEC(@Sql)

для 2000 скрипт вот
29 май 09, 13:10    [7243958]     Ответить | Цитировать Сообщить модератору
 Re: Как переменное количество столбцов в результате запроса?  [new]
akf
Member

Откуда: Недалекое заМКАДье
Сообщений: 355
iljy, была пара ошибок
SELECT [ID Испытания], COUNTER,F169      ,DK151     ,DK161           
FROM (
	SELECT t1.[ID Испытания], t1.COUNTER, t2.[Название параметра], t1.[Значение параметра] 
	FROM 	dbo.[Значения] t1 join dbo.[Параметры] t2 on t1.[ID Параметра] = t2.[ID Параметра] 
	WHERE [ID Испытания] = 21 ) p 
PIVOT (	max([Значение])	FOR [Название параметра] IN	
	(F169      ,DK151     ,DK161     ) ) 
	AS pvt order by [ID Испытания], COUNTER
где список параметров формируется, лишняя , появляется каждый раз в конце. Но это мелочи!
Надо ещё прикрутить в FOR свой SELECT для параметров именно выбранного испытания, с этим уж я справлюсь :)

Единственное, для меня не очевидно, почему
max([Значение])
возвращает не 1 значение (max), а весь список значений параметра?

P.s. Спасибо БОЛЬШОЕ за помощь!
29 май 09, 13:22    [7244072]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить