Блог

Я не видел подобного вопроса, но если есть такой, на который был дан ответ, пожалуйста, дайте мне знать.

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

Что мне нужно сделать, это динамически создать сводную таблицу и объединить ее с другой — или это то, что, как я думал, сработает.

Исходные данные работают аналогично этому (я изменил элементы для защиты данных моей компании):

Они хотят, чтобы данные выглядели в отчете следующим образом (для экономии места я использовал не все даты, но вы можете уловить идею):

Я создал временную таблицу и две динамические сводные таблицы. Обе таблицы будут работать отдельно, но как только я использую объединение ALL, я получаю сообщение об ошибке (я добавлю это ниже). Я включаю код, который я использовал для создания двух сводных таблиц. Может кто-нибудь сказать мне, что я делаю не так?

Возможно ли сделать это всего за один свод?

 /*
    Use dynamic SQL to find all 
    Issue Dates for column headings
*/
DECLARE @Jquery VARCHAR(8000)
DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],['   'Item 1'   ' '   (IssueDate)
                        FROM    #GroupData GroupData
                        ORDER BY '],['   'Item 1'   ' '   (IssueDate)
                        FOR XML PATH('')
                        ), 1, 2, '')   ']'

SET @query =
'SELECT * FROM
(
    SELECT LocationID, StoreName, StoreState AS State, "Item 1"   " "   (IssueDate) AS IssueDate, MoneyOrder
    FROM #GroupData GroupData
) MoneyOrderIssued
PIVOT (MAX(MoneyOrder) FOR IssueDate
IN (' @years ')) AS pvt'

DECLARE @queryMOUsed VARCHAR(4000)
DECLARE @MOUsedYear VARCHAR(2000)
SELECT  @MOUsedYear = STUFF(( SELECT DISTINCT
                        '],['   'Item 2'   ' '   (IssueDate)
                        FROM    #GroupData GroupData
                        ORDER BY '],['   'Item 2'   ' '   (IssueDate)
                        FOR XML PATH('')
                        ), 1, 2, '')   ']'

SET @queryMOUsed =
'SELECT * FROM
(
    SELECT LocationID, StoreName, StoreState AS State, "Item 2"   " "   (IssueDate) AS IssueDate, MOUsed
    FROM #GroupData GroupData
)SCRMoneyOrders
PIVOT (MAX(MOUsed) FOR IssueDate
IN (' @MOUsedYear ')) AS pvt'

SET @Jquery = @query   ' UNION ALL '    @queryMOUsed


EXECUTE (@query) -- Only in here to show that this works w/out UNION ALL
EXECUTE (@queryMOUsed) -- Only in here to show that this works w/out UNION ALL

EXECUTE (@Jquery)
  

Я получаю следующее сообщение об ошибке:

Предупреждение: Нулевое значение устраняется агрегированием или другой операцией НАБОРА. Сообщение 8114, уровень 16, состояние 5, ошибка в строке 1 при преобразовании типа данных varchar в bigint.

Моя идея заключается в том, что столбцы не совпадают (по количеству столбцов, порядку столбцов и типу данных). Если я правильно читаю ваш запрос, если даты выпуска для item1 и item2 не совпадают, вы все равно можете получить несоответствующие столбцы. Действительно трудно сказать, не видя выходных данных этих двух запросов.

Вы уверены, что не хотите ОБЪЕДИНЕНИЕ на основе идентификатора хранилища?

Что-то вроде :

 WITH Item1Data as (
--pivot query for item 1
),
Item2Data as (
 --pivot query for item 2
)
SELECT columns
FROM Item1DATA i1
LEFT JOIN Item2Data i2
ON i1.SoteID = i2.StoreID
  

Вот динамический запрос, который я выполнил. полученные столбцы генерируются данными:

  --Get string of aggregate columns for pivot.  The aggregate columns are the last 5 NRS Years.
                    DECLARE @aggcols NVARCHAR(MAX)

                    SELECT  @aggcols = STUFF(( SELECT   '],['
                                                          CAST(ny2.NRS_YEAR AS CHAR(4))
                                               FROM     mps.NRS_YEARS ny2
                                               WHERE    ny2.NRS_YEAR BETWEEN @NRS_Year
                                                        - 5 AND @NRS_Year
                                               ORDER BY '],['
                                                          CAST(ny2.NRS_YEAR AS CHAR(4))
                                             FOR
                                               XML PATH('') ) , 1 , 2 , '')
                              ']' ;

--While we're at it, get a sum of each year column.  we'll do a union query instead of  rollup because that's how we roll.

                    DECLARE @sumcols NVARCHAR(MAX) ;
                    SELECT  @sumcols = STUFF(( SELECT   ']),sum(['
                                                          CAST(ny2.NRS_YEAR AS CHAR(4))
                                               FROM     mps.NRS_YEARS ny2
                                               WHERE    ny2.NRS_YEAR BETWEEN @NRS_Year
                                                        - 5 AND @NRS_Year
                                               ORDER BY ']),sum(['
                                                          CAST(ny2.NRS_YEAR AS CHAR(4))
                                             FOR
                                               XML PATH('') ) , 1 , 3 , '')
                              '])' ;

                    DECLARE @Query NVARCHAR(MAX) ;
--Construct dynamic pivot query

                    SET @Query = N'SELECT MonthName as Month, '   @aggcols
                          N'
      into ##MonthHourPivot
FROM
 (SELECT nc.MONTHNAME, nc.MonthOfNRS_Yr, nc.NRS_YEAR, st.Hours
 FROM mps.NRS_Calendar nc 
 INNER JOIN dbo.StudentTime st
 ON nc.Date = /*00:00:00 AM*/ DATEADD(dd, DATEDIFF(dd, 0, /*On*/ st.EntryDateTime), 0)
 LEFT JOIN mps.vw_ScheduleRoomBuilding srb
 ON st.ScheduleID = srb.ScheduleID
 WHERE (st.EntryDateTime <= GETDATE() and st.SiteCode = '''   @SiteCode
                          N''' or '''   @SiteCode   N''' = ''All'')
 AND (srb.Abbreviation = '''   @Building   N''' or '''   @Building
                          N''' = ''All'')) p
 PIVOT
 (
 sum(p.Hours)
 FOR NRS_Year IN
( '   @aggcols   N' )
) AS pvt 
' ;

--Execute It.
                    EXECUTE(@Query) ;

                    SET @Query = N'Select [Month], '   @aggcols
                          N'FROM ##MonthHourPivot UNION ALL SELECT ''Total'' as [Month], '
                          @sumcols   ' FROM ##MonthHourPivot' ;
                     Execute (@Query);
  

Это кажется чем-то более подходящим для выполнения с помощью инструмента ETL.

Я не очень хорошо знаю набор инструментов Microsoft, но я предполагаю, что в их пакете для хранения данных есть что-то для этого. В программе Pentaho «Интеграция данных» (Kettle) вы бы использовали шаг денормализатора строк или шаг выравнивания строк.