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

Откуда:
Сообщений: 118
есть хранимая процедура для кросс-таблицы:
CREATE PROCEDURE [dbo].[crosstab] 
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)

-- Шаг 1: начало строки SQL.
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END
-- Шаг 2: Хранение ключей во временной таблице.
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table
EXEC (@keyssql)
-- Шаг 3: Средняя часть строки SQL.
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    ' round( avg(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
        '      END),3) AS [' + @key+']'
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key

END

-- Шаг 4: Конец строки SQL.
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows
SET NOCOUNT OFF
PRINT @sql  + @NEWLINE-- для отладки
EXEC (@sql)

Нужно добить итоговую колонку. помогите пожалуйста
5 май 11, 10:12    [10608026]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
Ну хоть кто-нибудь!
5 май 11, 11:50    [10608754]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
with cube ?
5 май 11, 12:20    [10608961]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
Артём Петросян,

что такое "итоговая колонка"? Это дополнительная строка? Тогда with rollup. Или это поле, в котором будет сумма всех остальных? Так вы же запрос генерите, оберните его подзапросом и во внешнем сделайте сумму.
5 май 11, 12:26    [10609015]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
iljy
Артём Петросян,

что такое "итоговая колонка"? Это дополнительная строка? Тогда with rollup. Или это поле, в котором будет сумма всех остальных? Так вы же запрос генерите, оберните его подзапросом и во внешнем сделайте сумму.


это не доп строка. это колонка. или как вы написали "это поле, в котором будет сумма всех остальных"
5 май 11, 12:43    [10609190]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
ну так вам уже ответили.. только не очнеь понятен смысл... если сумма всех остальных - зачем выводить 20 строк с одинаковым значением
5 май 11, 12:45    [10609208]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
Maxx
ну так вам уже ответили.. только не очнеь понятен смысл... если сумма всех остальных - зачем выводить 20 строк с одинаковым значением

это ведь кросс-таблица. и колонки могут быть разными.
5 май 11, 12:51    [10609249]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
Артём Петросян
iljy
Артём Петросян,

что такое "итоговая колонка"? Это дополнительная строка? Тогда with rollup. Или это поле, в котором будет сумма всех остальных? Так вы же запрос генерите, оберните его подзапросом и во внешнем сделайте сумму.


это не доп строка. это колонка. или как вы написали "это поле, в котором будет сумма всех остальных"

Если вы понимаете суть операции "кросс-таблица", то знаете, что все ваши поля сначала были записями
Что мешает сначала получить итог в виде записи, а потом уже конвертировать его в столбец как и остальные записи ?
5 май 11, 12:56    [10609293]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

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

легко сказать. процедуру кросс-таблицы я чужую взял
5 май 11, 13:07    [10609382]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Артём Петросян,
завал где ? Артем поробуйте привести тестовые данные и что в итоге вы хотите таки получить и в каком виде,потому как уже совершено тнепонятно что вы в итоге то хотите получить
5 май 11, 13:11    [10609413]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Glory
Member

Откуда:
Сообщений: 104751
Артём Петросян
Glory,

легко сказать. процедуру кросс-таблицы я чужую взял

Т.е. вы даже не разбирались, как она работает ?
5 май 11, 13:11    [10609423]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
кста с процедурой у вас тоже косяк :((
declare @k nvarchar(100)
create table #keys (i nvarchar(100) not null primary key)
insert into #keys
select CAST(number as nvarchar(100))
from master.dbo.spt_values
where type = 'P'

set @k = (select MIN(i) from #keys)
while @k is not null
begin
  print @k
  set @k = (select MIN(i) from #keys where i> @k)
end
drop table #keys

посмотрите на порядок выводимых записей, думаю вы такого порядка не ожидали......
5 май 11, 13:21    [10609523]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
сделал вот что:
ALTER PROCEDURE [dbo].[crosstab] 
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)

-- Шаг 1: начало строки SQL.
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END
-- Шаг 2: Хранение ключей во временной таблице.
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table
EXEC (@keyssql)
-- Шаг 3: Средняя часть строки SQL.
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    ' round( avg(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
        '      END),3) AS [' + @key+']'
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key

END
set @sql=@sql+','
-- Добавление итоговой колонки
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql +                    + @NEWLINE +
    ' round( avg(CASE WHEN '+
 'CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +'=N''' + @key +
           ''' and '+@sumcol+' is not null  THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
        '      END),3)+'
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key

END
set @sql=left(@sql,len(@sql)-1)+' as [Сумма баллов]'
-- Шаг 4: Конец строки SQL.
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows
SET NOCOUNT OFF
PRINT @sql  + @NEWLINE-- для отладки
EXEC (@sql)
добавил кода после: -- Добавление итоговой колонки
работает хорошо, но не во всех случаях. вот результирующая таблица:
комиссия | дом задание | конкурс кап. | музыкалка | сумма балов
Конкурсант1| 3.25 | 3.5 | 4 | 10.75
Конкурсант2| 3.667 | 4.5 | null | null

то есть если значения хоть одного не хватает, то он как null показывает
5 май 11, 13:52    [10609878]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
Артём Петросян
то есть если значения хоть одного не хватает, то он как null показывает

А как должен?
5 май 11, 13:58    [10609931]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
ISNULL( value, 0) ?
5 май 11, 13:59    [10609945]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
вот результирующий запрос:
SELECT
  name_konkursant AS Комиссия,
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ocenka
      END),3) AS [Домашнее задание],
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ocenka
      END),3) AS [Конкурс капитанов],
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ocenka
      END),3)+
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ocenka
      END),3) as [Сумма баллов]
FROM all_data
GROUP BY name_konkursant
ORDER BY name_konkursant

Сумма баллов формируется путем сложения запросов. как прежде чем суммировать проверить на null? вот этот кусочек:
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ocenka
      END),3)+
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ocenka
      END),3) as [Сумма баллов]
5 май 11, 14:06    [10610004]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
скажите пожалуста

1. у вас что может быть больше 1 оценки для студента по одному виду дисциплины ??? Прсто совершенно енпонятно использование avg - средняя оценка ????



 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ISNULL(ocenka, 0)
      END),3)+
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ISNULL(ocenka, 0)
      END),3) as [Сумма баллов]
5 май 11, 14:16    [10610088]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ISNULL(ocenka, 0)
          ELSE 0
      END),3)+
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ISNULL(ocenka, 0)
          ELSE 0
      END),3) as [Сумма баллов]
5 май 11, 14:17    [10610101]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
Maxx
скажите пожалуста

1. у вас что может быть больше 1 оценки для студента по одному виду дисциплины ??? Прсто совершенно енпонятно использование avg - средняя оценка ????



 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ISNULL(ocenka, 0)
      END),3)+
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ISNULL(ocenka, 0)
      END),3) as [Сумма баллов]


оценок столько, сколько членов жюри
5 май 11, 14:19    [10610117]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Артём Петросян,

и вам нужно среднюю ???
5 май 11, 14:20    [10610127]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
Maxx
round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ISNULL(ocenka, 0)
          ELSE 0
      END),3)+
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ISNULL(ocenka, 0)
          ELSE 0
      END),3) as [Сумма баллов]


ocenka null не принимает. null получается весь round. как его проверить? опять через case?
5 май 11, 14:21    [10610137]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
Maxx
Артём Петросян,

и вам нужно среднюю ???


конечно, судьи отправляют свои баллы. а программа расчитывает средний бал по каждой номинации. в сумме балов все эти средние баллы должны сложиться
5 май 11, 14:23    [10610145]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
таблицу тестовую и запонение данными плиз ..будет быстрее
5 май 11, 14:24    [10610157]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
Артём Петросян
Member

Откуда:
Сообщений: 118
вот результат работающий:
SELECT
  name_konkursant AS Комиссия,
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ocenka
      END),3) AS [Домашнее задание],
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ocenka
      END),3) AS [Конкурс капитанов],
 round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Музыкалка' THEN ocenka
      END),3) AS [Музыкалка],
case when round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ocenka
     END),3) is not null then 
round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Домашнее задание' THEN ocenka
     END),3)  else 0 end +
case when round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ocenka
     END),3) is not null then 
round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Конкурс капитанов' THEN ocenka
     END),3)  else 0 end +
case when round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Музыкалка' THEN ocenka
     END),3) is not null then 
round( avg(CASE CAST(name_nominacii AS nvarchar(100))
        WHEN N'Музыкалка' THEN ocenka
     END),3)  else 0 end  as [Сумма баллов]
FROM all_data
GROUP BY name_konkursant
ORDER BY name_konkursant

не много ли в нем case? можно ли упростить?
5 май 11, 14:33    [10610213]     Ответить | Цитировать Сообщить модератору
 Re: Добавить колонку Итого к кросс-таблице  [new]
iljy
Member

Откуда:
Сообщений: 8711
Артём Петросян,

а функцию isnull использовать не?
5 май 11, 14:34    [10610231]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить