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

Откуда: Москва
Сообщений: 187
Здравствуйте,

Подскажите пожалуйста, как добавить столбец в таблице в хранимой процедуре?
Создаю переменную таблицы в хранимой процедуре @tbl
В цикле WHILE хочу добавить несколько столбцов имя которых зависит от других данных в таблицах в базе данных с помощью команды:
ALTER TABLE @tbl ADD  @str1 nvarchar(max) NULL 

@tbl - переменная таблицы
@str1 - название добавляемого столбца

Но выдается ошибка:
MS SQL Managment Studio
Сообщение 102, уровень 15, состояние 1, строка 1
Неправильный синтаксис около конструкции "@tbl".


Вот текст хранимой процедуры
ALTER PROCEDURE [dbo].[SelectDataForOneForecast] 
	--@IdLinkBetweenOrganizationAndForecast int,
	@IdForecast int
AS
BEGIN
	DECLARE  @tbl TABLE (
		ID_Прибора  [int] IDENTITY NOT NULL,
		Название_прибора [nvarchar](max) NULL
	 )
	 DECLARE 	@BeginYear int,
				@EndYear int,
				--@TempYear int,
				@listYearsInForecast nvarchar(max),
				
				@pos1      int,
				@textpos1  int,
				@tmpstr1	  nvarchar(max),
				@str1      nvarchar(max),
				@lengthString1 int
	 
	SET @BeginYear = (SELECT [Год_начала_прогноза]  FROM [YanaBase_PISMP].[dbo].[Прогноз] WHERE ID =@IdForecast)	
	SET @EndYear = (SELECT [Год_конца_прогноза] FROM [YanaBase_PISMP].[dbo].[Прогноз] WHERE ID = @IdForecast)


	SET @listYearsInForecast = [dbo].[ReturnYearsInForecast] (@IdForecast)


	/* добавляем в конец пробел */
	SET @tmpstr1 = @listYearsInForecast + ' '        

	/* сколько символов в строе с пеердаными кодами получателей сообщения */
	SET @lengthString1 = datalength(@tmpstr1)

	/* организуем цикл по прохождения всей длинны списка переменных */
	WHILE @textpos1 <= (@lengthString1 - 1) 
		BEGIN -- цикл 13
			SET @pos1 = charindex(' ', @tmpstr1)
			SET @str1 = substring(@tmpstr1, 1, @pos1 - 1)
			SET @tmpstr1 = ltrim(substring(@tmpstr1, @pos1 + 1, len(@tmpstr1)))
			SET @pos1 = charindex(' ', @tmpstr1)
			SET @lengthString1 = datalength(@tmpstr1)
			
			ALTER TABLE @tbl ADD  @str1 nvarchar(max) NULL 
	 
		END -- цикл 13
	 
	SELECT * FROM  @tbl
 
END

В чем ошибка не ясно
6 фев 12, 12:57    [12039628]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Создавайте табличную переменную сразу нужной структуры. Или переходите на временные таблицы.
6 фев 12, 13:00    [12039660]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
Гавриленко Сергей Алексеевич
Создавайте табличную переменную сразу нужной структуры. Или переходите на временные таблицы.


Первый вариант отпадает, потому что на выходе всегда будет разное количество столбцов.
Например:
1 столбец: ID
2 столбец: Название
3 столбец: Год_2000
4 столбец: Год_2001
5 столбец: Год_2002
6 столбец: Год_2003

А вот, что значит временые таблицы - это как???
6 фев 12, 13:04    [12039728]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Solomka
А вот, что значит временые таблицы - это как???
Это так: create table #tbl (...
6 фев 12, 13:06    [12039747]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
iljy
Member

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

создайте таблицу ID, Name, Year, Value и не занимайтесь ерундой.
6 фев 12, 13:17    [12039868]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Программист-Любитель
Member

Откуда:
Сообщений: 16839
Solomka
Первый вариант отпадает, потому что на выходе всегда будет разное количество столбцов.
Например:
1 столбец: ID
2 столбец: Название
3 столбец: Год_2000
4 столбец: Год_2001
...

Это сводный запрос к нормализованным таблицам. Не в ту сторону идете.
6 фев 12, 15:08    [12041002]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
Гавриленко Сергей Алексеевич
Solomka
А вот, что значит временые таблицы - это как???
Это так: create table #tbl (...



Заменила на временную таблицу:
	CREATE TABLE #TempTable
	(
		ID_Прибора  [int] IDENTITY NOT NULL,
		Название_прибора [nvarchar](max) NULL
	)

Теперь добавляю столбец такой командой:
ALTER TABLE #TempTable ADD   @str1  nvarchar(max) NULL

Однако выдается ошибка:
Сообщение 102, уровень 15, состояние 1, процедура SelectDataForOneForecast, строка 55
Неправильный синтаксис около конструкции "@str1".

Пыталась заменить код на:
SET @textCommand  = ' ALTER TABLE #TempTable ADD  ' +@str1 + ' nvarchar(max) NULL '
EXEC (@textCommand)

Ошибку так не выдает но и столбцы не добавляет...
6 фев 12, 15:20    [12041133]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
Программист-Любитель
Это сводный запрос к нормализованным таблицам. Не в ту сторону идете.

сводный запрос к нормализованным таблицам - что это такое и с чем его едят?
Яндекс не дал мне членораздельного ответа.
Что имелось в виду?
6 фев 12, 15:23    [12041163]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Solomka, может лучше огласить изначальную задачу? Зачем вам нужна таблица с переменным кол-вом столбцев, которая генерится в процедуре? Т.к. то, что вы делаете - несколько странные вещи.
6 фев 12, 15:42    [12041358]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
kDnZP
Solomka, может лучше огласить изначальную задачу? Зачем вам нужна таблица с переменным кол-вом столбцев, которая генерится в процедуре? Т.к. то, что вы делаете - несколько странные вещи.

Изначально имеется такая таблица:
Картинка с другого сайта.
Потом для формирования таблицы нужно отобрать и обработать данные из БД:
Картинка с другого сайта.

В итоге хотелось бы получить:
Картинка с другого сайта.

Чтобы не рябило в глазах, тут тоже самое, только ID строк:
Картинка с другого сайта.
Я понятно изъяснила начальную задачу?
6 фев 12, 16:51    [12042260]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Sergey Sizov
Member

Откуда:
Сообщений: 1579
Solomka
Я понятно изъяснила начальную задачу?
Нет. Вы нам предлагаете ручками набрать текст с Ваших картинок? А сами Вы копипастнуть никак не можете? А типы полей мы сами должны придумать?
6 фев 12, 16:56    [12042301]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
kDnZP
Solomka, может лучше огласить изначальную задачу? Зачем вам нужна таблица с переменным кол-вом столбцев, которая генерится в процедуре? Т.к. то, что вы делаете - несколько странные вещи.

Ой вторая таблица не правильно выглядит:
Картинка с другого сайта.
6 фев 12, 17:10    [12042426]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Solomka, и нафига тут таблицы в процедуре? Даже если предположить, что кол-во годов у вас заранее неизвестно, то вам просто нужна динамика, в которой собрать итоговый результат. Вот точно помню, что пару дней подобная задача была, читать тут... Если что-то все равно непонятно, тогда вместо картинок таки давайте тестовые данные в виде скриптов.
6 фев 12, 17:14    [12042454]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
Sergey Sizov
Solomka
Я понятно изъяснила начальную задачу?
Нет. Вы нам предлагаете ручками набрать текст с Ваших картинок? А сами Вы копипастнуть никак не можете? А типы полей мы сами должны придумать?

Ой, извените,
Я думала что так лучше будет....
Сейчас дам:

Создание таблицы:

CREATE TABLE [dbo].[Данные_для_прогнозов](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Соотношение_прогноза_и_предприятия_ID] [int] NOT NULL,
[Прибор_ID] [int] NULL,
[Год] [int] NULL,
[Количество] [int] NULL,
[Стоимость] [money] NULL,
CONSTRAINT [PK_Данные_для_прогнозов] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Запрос, который берет все записи
SELECT [ID]
,[Соотношение_прогноза_и_предприятия_ID]
,[Прибор_ID]
,[Год]
,[Количество]
,[Стоимость]
FROM [YanaBase_PISMP].[dbo].[Данные_для_прогнозов]

Результат:

ID Соотношение_прогноза_и_предприятия_ID Прибор_ID Год Количество Стоимость
1 27 183 2000 2 1,5000
2 27 183 2001 3 1,5000
3 27 183 2002 4 1,5000
4 29 11 2000 2 1,5000
5 29 11 2001 3 1,5000
6 29 11 2002 4 1,5000
7 29 12 2000 2 1,5000
8 29 12 2001 3 1,5000
9 29 12 2002 4 1,5000
10 29 13 2000 2 1,5000
11 29 13 2001 3 1,5000
12 29 13 2002 4 1,5000
13 29 14 2000 2 1,5000
14 29 14 2001 3 1,5000
15 29 14 2002 4 1,5000
16 29 15 2000 2 1,5000
17 29 15 2001 3 1,5000
18 29 15 2002 4 1,5000
19 29 16 2000 2 1,5000
20 29 16 2001 3 1,5000
21 29 16 2002 4 1,5000


Данные обрабатываются и отфильтровываются перед тем как записать в таблицу:

SELECT [ID]
,[Прибор_ID]
,[Год]
,[Количество]
,[Стоимость]
,[Количество] * [Стоимость] AS Суммарная_стоимость
, CONVERT(nvarchar(max),[ID]) + '/' +
CONVERT(nvarchar(max),Стоимость) +'/' +
CONVERT(nvarchar(max), ([Количество] ) )+'/' +
CONVERT(nvarchar(max), ([Количество] * [Стоимость]) )
AS То_что_пойдет_в_ячейку
FROM [YanaBase_PISMP].[dbo].[Данные_для_прогнозов]
WHERE [Соотношение_прогноза_и_предприятия_ID] = 29

Результат запроса:
ID Прибор_ID Год Количество Стоимость Суммарная_стоимость То_что_пойдет_в_ячейку
4 11 2000 2 1,50 3,00 4/1.50/2/3.00
5 11 2001 3 1,50 4,50 5/1.50/3/4.50
6 11 2002 4 1,50 6,00 6/1.50/4/6.00
7 12 2000 2 1,50 3,00 7/1.50/2/3.00
8 12 2001 3 1,50 4,50 8/1.50/3/4.50
9 12 2002 4 1,50 6,00 9/1.50/4/6.00
10 13 2000 2 1,50 3,00 10/1.50/2/3.00
11 13 2001 3 1,50 4,50 11/1.50/3/4.50
12 13 2002 4 1,50 6,00 12/1.50/4/6.00
13 14 2000 2 1,50 3,00 13/1.50/2/3.00
14 14 2001 3 1,50 4,50 14/1.50/3/4.50
15 14 2002 4 1,50 6,00 15/1.50/4/6.00
16 15 2000 2 1,50 3,00 16/1.50/2/3.00
17 15 2001 3 1,50 4,50 17/1.50/3/4.50
18 15 2002 4 1,50 6,00 18/1.50/4/6.00
19 16 2000 2 1,50 3,00 19/1.50/2/3.00
20 16 2001 3 1,50 4,50 20/1.50/3/4.50
21 16 2002 4 1,50 6,00 21/1.50/4/6.00

Вот что бы хотелось получить на выходе:
Прибор_ID 2000 2001 2002
11 4/1.50/2/3.00 5/1.50/3/4.50 6/1.50/4/6.00
12 7/1.50/2/3.00 8/1.50/3/4.50 9/1.50/4/6.00
13 10/1.50/2/3.00 11/1.50/3/4.50 12/1.50/4/6.00
14 13/1.50/2/3.00 14/1.50/3/4.50 15/1.50/4/6.00
15 16/1.50/2/3.00 17/1.50/3/4.50 18/1.50/4/6.00
16 19/1.50/2/3.00 20/1.50/3/4.50 21/1.50/4/6.00


То же самое, чтобы глаза не рябило. Здесь только ID
Прибор_ID 2000 2001 2002
11 4 5 6
12 7 8 9
13 10 11 12
14 13 14 15
15 16 17 18
16 19 20 21
6 фев 12, 17:27    [12042556]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Solomka, спасиба бальшое, особенно шоб глаза не рябило)))))))))))))))))
Учу бесплатно:
--BEGIN TRAN

CREATE TABLE [dbo].[Данные_для_прогнозов]
    (
      [ID] [int] IDENTITY(1, 1)
                 NOT NULL,
      [Соотношение_прогноза_и_предприятия_ID] [int] NOT NULL,
      [Прибор_ID] [int] NULL,
      [Год] [int] NULL,
      [Количество] [int] NULL,
      [Стоимость] [money] NULL,
      CONSTRAINT [PK_Данные_для_прогнозов] PRIMARY KEY CLUSTERED ( [ID] ASC )
    )
GO
SET IDENTITY_INSERT [dbo].[Данные_для_прогнозов] ON
INSERT INTO [dbo].[Данные_для_прогнозов](ID, [Соотношение_прогноза_и_предприятия_ID], [Прибор_ID], [Год], [Количество], [Стоимость])
SELECT 1, 27, 183, 2000, 2, 1.5000 UNION ALL SELECT
2, 27, 183, 2001, 3, 1.5000 -- UNION ALL SELECT ...
SET IDENTITY_INSERT [dbo].[Данные_для_прогнозов] OFF

--SELECT * FROM [dbo].[Данные_для_прогнозов]
--ROLLBACK

Дальше самостоятельно. И еще раз: Вы хоть слегка напряглись? И почитали по данной мною ссылке?
6 фев 12, 17:36    [12042630]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
kDnZP
Solomka, спасиба бальшое, особенно шоб глаза не рябило)))))))))))))))))
Учу бесплатно:

Дальше самостоятельно. И еще раз: Вы хоть слегка напряглись? И почитали по данной мною ссылке?


Спасибо за ссылку,
Пока особо не понятно, нужно время чтобы покапаться и понять всё там.
Изначально не смотрела туда потому что думала, что не обойтись без генерации таблиц.
я реньше ничего не знала о существовании PIVOT
7 фев 12, 00:19    [12044294]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
kDnZP
Solomka, спасиба бальшое, особенно шоб глаза не рябило)))))))))))))))))
Учу бесплатно:
Дальше самостоятельно. И еще раз: Вы хоть слегка напряглись? И почитали по данной мною ссылке?


Даже уже что-то получилось... Только не совсем то...
Прибор_ID200020012002
114NULLNULL
11NULL5NULL
11NULLNULL6
127NULLNULL
12NULL8NULL
12NULLNULL9
1310NULLNULL
13NULL11NULL
13NULLNULL12
1413NULLNULL
14NULL14NULL
14NULLNULL15
1516NULLNULL
15NULL17NULL
15NULLNULL18
1619NULLNULL
16NULL20NULL
16NULLNULL21


А хотелось бы получить:

Прибор_ID200020012002
11456
12789
13101112
14131415
15161718
16192021


Текст запроса:
SELECT [Прибор_ID], [2000],[2001],[2002] 
FROM
    (SELECT  [ID]
      ,[Соотношение_прогноза_и_предприятия_ID]
      ,[Прибор_ID]
      ,[Год]
      ,[Количество]  
       FROM [YanaBase_PISMP].[dbo].[Данные_для_прогнозов] 
       WHERE [Соотношение_прогноза_и_предприятия_ID]=29
       )
    AS SourceTable
PIVOT
( AVG([ID])
FOR [Год]
    IN ( [2000],[2001],[2002] )) AS ResultTable

Мне кажется, что я чего то недоучла...
Быть может нужен где-то какой-нибудь GROUP BY или ещё что-то?
Отталкивалась от примера, поэтому и поставила функцию AVG - может надо было другую функцию использовать?
7 фев 12, 13:39    [12046641]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
iljy
Member

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

у вас лишние столбцы в выборке. Поставьте во внешнем SELECT * вместо списка полей, сразу станет понятно.
7 фев 12, 13:59    [12046835]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
iljy
Solomka,

у вас лишние столбцы в выборке. Поставьте во внешнем SELECT * вместо списка полей, сразу станет понятно.


Да Вы были правы, когда я убрала столбец количество, то всё встало, так как надо...
Однако мне нужны данные из этих столбцов.
Чтобы было более близко к тексту то это мой запрос.
SELECT *
FROM
    (SELECT  [ID],
       [Прибор_ID],
      CONVERT(nvarchar(max),[ID]) + '/' + CONVERT(nvarchar(max),Стоимость) +'/' + CONVERT(nvarchar(max), ([Количество]  ) )+'/' + CONVERT(nvarchar(max), ([Количество] * [Стоимость]) )
      AS То_что_пойдет_в_ячейку
      ,[Год]        
       FROM [YanaBase_PISMP].[dbo].[Данные_для_прогнозов] 
       WHERE [Соотношение_прогноза_и_предприятия_ID]=29 )
    AS SourceTable
PIVOT
(  AVG([ID])

FOR [Год] IN ( [2000],[2001],[2002] )

)AS ResultTable

На выходе получается:
[Прибор_ID]То_что_пойдет_в_ячейку[2000][2001][2002]
1310/1.50/2/3.0010NULLNULL
1311/1.50/3/4.50NULL11NULL
1312/1.50/4/6.00NULLNULL12
1413/1.50/2/3.0013NULLNULL
1414/1.50/3/4.50NULL14NULL
1415/1.50/4/6.00NULLNULL15
1516/1.50/2/3.0016NULLNULL
1517/1.50/3/4.50NULL17NULL
1518/1.50/4/6.00NULLNULL18
1619/1.50/2/3.0019NULLNULL
1620/1.50/3/4.50NULL20NULL
1621/1.50/4/6.00NULLNULL21
114/1.50/2/3.004NULLNULL
115/1.50/3/4.50NULL5NULL
116/1.50/4/6.00NULLNULL6
127/1.50/2/3.007NULLNULL
128/1.50/3/4.50NULL8NULL
129/1.50/4/6.00NULLNULL9

А в идеале мне нужно:
114/1.50/2/3.005/1.50/3/4.506/1.50/4/6.00
127/1.50/2/3.008/1.50/3/4.509/1.50/4/6.00
1310/1.50/2/3.0011/1.50/3/4.5012/1.50/4/6.00
1413/1.50/2/3.0014/1.50/3/4.5015/1.50/4/6.00
1516/1.50/2/3.0017/1.50/3/4.5018/1.50/4/6.00
1619/1.50/2/3.0020/1.50/3/4.50 21/1.50/4/6.00


Можно ли это как то получить?

Это исходная таблица:
CREATE TABLE [dbo].[Данные_для_прогнозов](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Соотношение_прогноза_и_предприятия_ID] [int] NOT NULL,
	[Прибор_ID] [int] NULL,
	[Год] [int] NULL,
	[Количество] [int] NULL,
	[Стоимость] [money] NULL,
 CONSTRAINT [PK_Данные_для_прогнозов] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (27,183,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (27,183,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (27,183,2002,4,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,11,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,11,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,11,2002,4,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,12,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,12,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,12,2002,4,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,13,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,13,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,13,2002,4,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,14,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,14,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,14,2002,4,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,15,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,15,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,15,2002,4,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,16,2000,2,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,16,2001,3,1.50)
INSERT INTO [Данные_для_прогнозов] ([Соотношение_прогноза_и_предприятия_ID] ,[Прибор_ID] ,[Год] ,[Количество],[Стоимость]) VALUES (29,16,2002,4,1.50)


А какую тогда можно применить функцию к полю "то_что_пойдет_в_ячейку"?
AVG - это же функция для чисел...
7 фев 12, 15:39    [12047750]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
iljy
Member

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

тогда зачем вы группируете не то поле, которое вам нужно, а какой-то ID?
7 фев 12, 16:15    [12048116]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Maksim8
Member

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

попробуй
...
PIVOT
( MAX([То_что_пойдет_в_ячейку])
...
7 фев 12, 18:04    [12049357]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
Solomka
Member

Откуда: Москва
Сообщений: 187
Спасибо всем, кто писал в этой теме!!!!
Полученное решение оказалось совершенно неожиданный, поскольку я думала эта задача решается через генерирование таблицы.

Когда убрала ID, то запрос приобрел форму:
SELECT *
FROM
    (SELECT   
       [Прибор_ID],
      CONVERT(nvarchar(max),[ID]) + '/' + CONVERT(nvarchar(max),Стоимость) +'/' + CONVERT(nvarchar(max), ([Количество]  ) )+'/' + CONVERT(nvarchar(max), ([Количество] * [Стоимость]) )
      AS То_что_пойдет_в_ячейку
      ,[Год]        
       FROM [YanaBase_PISMP].[dbo].[Данные_для_прогнозов] 
       WHERE [Соотношение_прогноза_и_предприятия_ID]=29 )
    AS SourceTable
PIVOT
( MAX([То_что_пойдет_в_ячейку] )
FOR [Год] IN ( [2000],[2001],[2002] )
)AS ResultTable

И всё получилось так как надо...

Но вот только единственный вопрос ради любопытства остался.
В одном случае, количество годов(то есть теперь уже столбцов) будет "[2000], [2001], [2002]" в другом случае "[2001],[2002],[2003],[2004], [2005]".
Можно ли как то обойтись без динамического SQL?

ALTER PROCEDURE [dbo].[SelectDataForOneForecast] 
	@IdLinkBetweenOrganizationAndForecast int,
	@IdForecast int
AS
BEGIN
	DECLARE  @tbl TABLE (
		ID_Прибора  [int] IDENTITY NOT NULL,
		Название_прибора [nvarchar](max) NULL
	 )
	 DECLARE 	@BeginYear int,
				@EndYear int,
				@TempYear int,
				@listYearsInForecast nvarchar(max),				
				@textCommand nvarchar(max) 
	 
	SET @BeginYear = (SELECT [Год_начала_прогноза]  FROM [YanaBase_PISMP].[dbo].[Прогноз] WHERE ID =@IdForecast)	
	SET @EndYear = (SELECT [Год_конца_прогноза] FROM [YanaBase_PISMP].[dbo].[Прогноз] WHERE ID = @IdForecast)

	
	SET @TempYear = @BeginYear
	SET @listYearsInForecast = ''
	
	WHILE (@TempYear<=@EndYear)
	BEGIN
		SET @listYearsInForecast = @listYearsInForecast + '['+ CONVERT(nvarchar(max), @TempYear)  +']'
		IF (@TempYear<@EndYear)
		BEGIN
			SET @listYearsInForecast = @listYearsInForecast + ', '
		END
		SET @TempYear = @TempYear + 1
	END	
	
    -- @listYearsInForecast содержит года через запятую [2000],[2001],[2002],[2003],[2004], ...

    SET @textCommand = 'SELECT * FROM (SELECT [Прибор_ID], '
    SET @textCommand = @textCommand + ' CONVERT(nvarchar(max),[ID]) + ' + N'''/''' 
    SET @textCommand = @textCommand + ' + CONVERT(nvarchar(max),Стоимость) +'+ N'''/''' +' + CONVERT(nvarchar(max), ([Количество]  ) )+'
    SET @textCommand = @textCommand +  N'''/''' +' + CONVERT(nvarchar(max), ([Количество] * [Стоимость]) ) '
    SET @textCommand = @textCommand + ' AS То_что_пойдет_в_ячейку, [Год] FROM [YanaBase_PISMP].[dbo].[Данные_для_прогнозов] '
    SET @textCommand = @textCommand + ' WHERE [Соотношение_прогноза_и_предприятия_ID]=' +  CONVERT(nvarchar(max), @IdLinkBetweenOrganizationAndForecast )
    SET @textCommand = @textCommand + ' )  AS SourceTable PIVOT (MAX([То_что_пойдет_в_ячейку]) FOR [Год] IN ( ' 

    SET @textCommand = @textCommand + @listYearsInForecast 

    SET @textCommand = @textCommand +  ' ) )AS ResultTable'
   
    
 EXEC(@textCommand)
 
END


Или чудес не бывает?
8 фев 12, 10:57    [12052097]     Ответить | Цитировать Сообщить модератору
 Re: Как добавить столбец в таблице в хранимой процедуре?  [new]
iljy
Member

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

таблица с неизвестным количеством полей обязательно означает динамику. Пример можно посмотреть тут 7242227.
8 фев 12, 11:03    [12052132]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить