SQL Server 2008 Pivot, нет агрегирования, сложные данные

Я видел много сообщений «Pivot, No Agg», но все они, похоже, содержат некоторые довольно простые данные для поворота, поэтому решения работают достаточно хорошо и легко. Но как насчет того, когда данные не так просты?

Я хотел бы включить это:

wwnID Tenant WeekOfTheMonth ReportingDate TotalEmployeesPerBranch TotalOpenCount TotalClosedCount OpenCount_TitleAndEscrow ClosedCount_TitleAndEscrow OpenCount_EscrowOnly ClosedCount_EscrowOnly OpenCount_PreListingTask ClosedCount_PreListingTask OFPE CFPE OpenCount_TitleOnly ClosedCount_TitleOnly CurrentBusinessDay TotalBusinessDaysMTD ReportingDateId CreatedDate ----------- -------------------------------------------------- -------------- ----------------------- --------------------------------------- -------------- ---------------- ------------------------ -------------------------- -------------------- ---------------------- ------------------------ -------------------------- --------------------------------------- --------------------------------------- ------------------- --------------------- ------------------ -------------------- --------------- ----------------------- 3 King 1 2014-08-08 00:00:00.000 144.00 235 0 137 0 64 0 34 0 4.81 0.00 270 0 7 21 411 2014-09-05 08:53:11.313 5 King 2 2014-08-15 00:00:00.000 150.00 399 0 224 0 112 0 63 0 4.62 0.00 524 0 12 21 412 2014-09-05 08:53:19.573 7 King 3 2014-08-22 00:00:00.000 150.00 584 0 335 0 159 0 90 0 4.76 0.00 721 0 17 21 413 2014-09-05 08:53:26.980 9 King 4 2014-08-31 00:00:00.000 150.00 797 0 436 0 226 0 135 0 5.18 0.00 946 0 21 21 414 2014-09-05 08:53:35.593 4 Pierce 1 2014-08-08 00:00:00.000 21.00 85 0 31 0 39 0 15 0 12.00 0.00 54 0 7 21 411 2014-09-05 08:53:11.670 6 Pierce 2 2014-08-15 00:00:00.000 22.00 160 0 62 0 74 0 24 0 12.41 0.00 83 0 12 21 412 2014-09-05 08:53:20.000 8 Pierce 3 2014-08-22 00:00:00.000 22.00 222 0 82 0 107 0 33 0 12.41 0.00 127 0 17 21 413 2014-09-05 08:53:27.407 10 Pierce 4 2014-08-31 00:00:00.000 23.00 272 0 99 0 130 0 43 0 10.96 0.00 159 0 21 21 414 2014-09-05 08:53:36.063 

в это:

 Data Types Week 1 Week 2 Week 3 Week 4 ------------------------------- ----------- ----------- ----------- ----------- Tenant King King King King ReportingDate 8/8/2014 8/15/2014 8/22/2014 8/31/2014 TotalEmployeesPerBranch 144 150 150 150 TotalOpenCount 235 399 584 797 TotalClosedCount 0 0 0 0 OpenCount_TitleAndEscrow 137 224 335 436 ClosedCount_TitleAndEscrow 0 0 0 0 OpenCount_EscrowOnly 64 112 159 226 ClosedCount_EscrowOnly 0 0 0 0 OpenCount_PreListingTask 34 63 90 135 ClosedCount_PreListingTask 0 0 0 0 OFPE 4.81 4.62 4.76 5.18 CFPE 0 0 0 0 OpenCount_TitleOnly 270 524 721 946 ClosedCount_TitleOnly 0 0 0 0 CurrentBusinessDay 7 12 17 21 TotalBusinessDaysMTD 21 21 21 21 ReportingDateId 411 412 413 414 CreatedDate 9/5/2014 9/5/2014 9/5/2014 9/5/2014 Tenant Pierce Pierce Pierce Pierce ReportingDate 8/8/2014 8/15/2014 8/22/2014 8/31/2014 TotalEmployeesPerBranch 21 22 22 23 TotalOpenCount 85 160 222 272 TotalClosedCount 0 0 0 0 OpenCount_TitleAndEscrow 31 62 82 99 ClosedCount_TitleAndEscrow 0 0 0 0 OpenCount_EscrowOnly 39 74 107 130 ClosedCount_EscrowOnly 0 0 0 0 OpenCount_PreListingTask 15 24 33 43 ClosedCount_PreListingTask 0 0 0 0 OFPE 12 12.41 12.41 10.96 CFPE 0 0 0 0 OpenCount_TitleOnly 54 83 127 159 ClosedCount_TitleOnly 0 0 0 0 CurrentBusinessDay 7 12 17 21 TotalBusinessDaysMTD 21 21 21 21 ReportingDateId 411 412 413 414 CreatedDate 9/5/2014 9/5/2014 9/5/2014 9/5/2014 

Я пробовал несколько способов поворота, и никто из них, похоже, не делает этого, но если кто-то знает способ сделать это, это будет фантастично!

Спасибо заранее!


ОБНОВИТЬ


Это прекрасно работает! (объявления начальных переменных для предложения where в конце)

 DECLARE @ReportDate DATETIME = '2014-08-31'; DECLARE @RepDateId INT = ( SELECT MAX([WRD].[ReportingDateID]) FROM [SMS].[dbo].[WSOBReportingDates] AS WRD WHERE ( [WRD].[ReportingDate] <= @ReportDate ) AND ( [WRD].[Submitted] = 1 ) ); DECLARE @WSOBRepDate DATETIME = ( SELECT [WRD].[ReportingDate] FROM [SMS].[dbo].[WSOBReportingDates] AS WRD WHERE [WRD].[ReportingDateID] = @RepDateId ); DECLARE @WSOBStartDate DATETIME = DATEADD(mm, DATEDIFF(mm, 0, @WSOBRepDate), 0); SELECT Datatype , MAX(CASE WHEN WeekOfTheMonth = 1 THEN value ELSE '0' END) Week1 , MAX(CASE WHEN WeekOfTheMonth = 2 THEN value ELSE '0' END) Week2 , MAX(CASE WHEN WeekOfTheMonth = 3 THEN value ELSE '0' END) Week3 , MAX(CASE WHEN WeekOfTheMonth = 4 THEN value ELSE '0' END) Week4 FROM ( SELECT WeekOfTheMonth , DataType , Value , SortOrder , Sequence = ROW_NUMBER() OVER ( PARTITION BY WeekOfTheMonth ORDER BY wwnId ) FROM [dbo].[SSRS_WSOBWeeklyNumbers] AS SWWN CROSS APPLY ( SELECT 'Tenant' , [SWWN].[Tenant] , 1 UNION ALL SELECT 'ReportingDate' , CONVERT(VARCHAR(10), [SWWN].[ReportingDate], 120) , 2 UNION ALL SELECT 'TotalEmployeesPerBranch' , CAST([SWWN].[TotalEmployeesPerBranch] AS VARCHAR(10)) , 3 UNION ALL SELECT 'TotalOpenCount' , CAST([SWWN].[TotalOpenCount] AS VARCHAR(10)) , 4 UNION ALL SELECT 'TotalClosedCount' , CAST([SWWN].[TotalClosedCount] AS VARCHAR(10)) , 5 UNION ALL SELECT 'OpenCount_TitleAndEscrow' , CAST([SWWN].[OpenCount_TitleAndEscrow] AS VARCHAR(10)) , 6 UNION ALL SELECT 'ClosedCount_TitleAndEscrow' , CAST([SWWN].[ClosedCount_TitleAndEscrow] AS VARCHAR(10)) , 7 UNION ALL SELECT 'OpenCount_EscrowOnly' , CAST([SWWN].[OpenCount_EscrowOnly] AS VARCHAR(10)) , 8 UNION ALL SELECT 'ClosedCount_EscrowOnly' , CAST([SWWN].[ClosedCount_EscrowOnly] AS VARCHAR(10)) , 9 UNION ALL SELECT 'OpenCount_PreListingTask' , CAST([SWWN].[OpenCount_PreListingTask] AS VARCHAR(10)) , 10 UNION ALL SELECT 'ClosedCount_PreListingTask' , CAST([SWWN].[ClosedCount_PreListingTask] AS VARCHAR(10)) , 11 UNION ALL SELECT 'OFPE' , CAST([SWWN].[OFPE] AS VARCHAR(10)) , 12 UNION ALL SELECT 'CFPE' , CAST([SWWN].[CFPE] AS VARCHAR(10)) , 13 UNION ALL SELECT 'OpenCount_TitleOnly' , CAST([SWWN].[OpenCount_TitleOnly] AS VARCHAR(10)) , 14 UNION ALL SELECT 'ClosedCount_TitleOnly' , CAST([SWWN].[ClosedCount_TitleOnly] AS VARCHAR(10)) , 15 UNION ALL SELECT 'CurrentBusinessDay' , CAST([SWWN].[CurrentBusinessDay] AS VARCHAR(10)) , 16 UNION ALL SELECT 'TotalBusinessDaysForMonth' , CAST([SWWN].[TotalBusinessDaysMTD] AS VARCHAR(10)) , 17 UNION ALL SELECT 'ReportingDateId' , CAST([SWWN].[ReportingDateId] AS VARCHAR(10)) , 18 UNION ALL SELECT 'CreatedDate' , CAST([SWWN].[CreatedDate] AS VARCHAR(10)) , 19 ) c ( DataType, Value, SortOrder ) WHERE [SWWN].[ReportingDate] BETWEEN @WSOBStartDate AND @ReportDate ) d GROUP BY DataType , Sequence , SortOrder ORDER BY Sequence , SortOrder; 

и приводит к:

 Тип данных Неделя1 Неделя2 Неделя3 Неделя4
 -------------------------- ------------------------ -------------------------- ------------------------ -------------------------- ------------------------ -------------------------- ------------------------ --------------------------
 Арендатор Кинг Кинг Кинг Кинг
 ReportingDate 2014-08-08 2014-08-15 2014-08-22 2014-08-31
 TotalEmployeesPerBranch 144.00 150.00 150.00 150.00
 TotalOpenCount 235 399 584 797
 TotalClosedCount 0 0 0 0
 OpenCount_TitleAndEscrow 137 224 335 436
 ClosedCount_TitleAndEscrow 0 0 0 0
 OpenCount_EscrowOnly 64 112 159 226
 ClosedCount_EscrowOnly 0 0 0 0
 OpenCount_PreListingTask 34 63 90 135
 ClosedCount_PreListingTask 0 0 0 0
 OFPE 4,81 4,62 4,76 5,18
 CFPE 0,00 0,00 0,00 0,00
 OpenCount_TitleOnly 270 524 721 946
 ClosedCount_TitleOnly 0 0 0 0
 CurrentBusinessDay 7 12 17 21
 TotalBusinessDaysForMonth 21 21 21 21
 ReportingDateId 411 412 413 414
 CreatedDate Sep 5 201 Sep 5 201 Sep 5 201 Sep 5 201
 Арендатор Пирс Пирс Пирс Пирс
 ReportingDate 2014-08-08 2014-08-15 2014-08-22 2014-08-31
 TotalEmployeesPerBranch 21.00 22.00 22.00 23.00
 TotalOpenCount 85 160 222 272
 TotalClosedCount 0 0 0 0
 OpenCount_TitleAndEscrow 31 62 82 99
 ClosedCount_TitleAndEscrow 0 0 0 0
 OpenCount_EscrowOnly 39 74 107 130
 ClosedCount_EscrowOnly 0 0 0 0
 OpenCount_PreListingTask 15 24 33 43
 ClosedCount_PreListingTask 0 0 0 0
 OFPE 12,00 12,41 12,41 10,96
 CFPE 0,00 0,00 0,00 0,00
 OpenCount_TitleOnly 54 83 127 159
 ClosedCount_TitleOnly 0 0 0 0
 CurrentBusinessDay 7 12 17 21
 TotalBusinessDaysForMonth 21 21 21 21
 ReportingDateId 411 412 413 414
 CreatedDate Sep 5 201 Sep 5 201 Sep 5 201 Sep 5 201

Спасибо тонны за ответ!

Сначала вам нужно выполнить UNPIVOT все эти столбцы, а затем преобразовать ваши недели в новые столбцы. Но для того, чтобы UNPIVOT данные, вам придется преобразовать все типы данных в одно и то же.

Поскольку вы используете SQL Server 2008, вы можете использовать CROSS APPLY для отказа. Основным синтаксисом будет:

 select WeekOfTheMonth, DataType, Value from yourtable cross apply ( select 'Tenant', Tenant union all select 'ReportingDate', convert(varchar(10), ReportingDate, 120) union all select 'TotalEmployeesPerBranch', cast(TotalEmployeesPerBranch as varchar(10)) union all select 'TotalOpenCount', cast(TotalOpenCount as varchar(10)) union all select 'TotalClosedCount', cast(TotalClosedCount as varchar(10)) union all select 'OpenCount_TitleAndEscrow', cast(OpenCount_TitleAndEscrow as varchar(10)) union all select 'ClosedCount_TitleAndEscrow', cast(ClosedCount_TitleAndEscrow as varchar(10)) union all select 'OpenCount_EscrowOnly', cast(OpenCount_EscrowOnly as varchar(10)) union all select 'ClosedCount_EscrowOnly', cast(ClosedCount_EscrowOnly as varchar(10)) union all select 'OpenCount_PreListingTask', cast(OpenCount_PreListingTask as varchar(10)) --- union all more columns ) c (DataType, value); 

См. SQL Fiddle with Demo . Затем вы примените PIVOT к своим неделям:

 select DataType, Week1 = [1], Week2 = [2], Week3 = [3], Week4 = [4] from ( select WeekOfTheMonth, DataType, Value, so, seq = row_number() over(partition by WeekOfTheMonth order by wwnId) from yourtable cross apply ( select 'Tenant', Tenant, 1 union all select 'ReportingDate', convert(varchar(10), ReportingDate, 120), 2 union all select 'TotalEmployeesPerBranch', cast(TotalEmployeesPerBranch as varchar(10)), 3 union all select 'TotalOpenCount', cast(TotalOpenCount as varchar(10)), 4 union all select 'TotalClosedCount', cast(TotalClosedCount as varchar(10)), 5 union all select 'OpenCount_TitleAndEscrow', cast(OpenCount_TitleAndEscrow as varchar(10)), 6 union all select 'ClosedCount_TitleAndEscrow', cast(ClosedCount_TitleAndEscrow as varchar(10)), 7 union all select 'OpenCount_EscrowOnly', cast(OpenCount_EscrowOnly as varchar(10)),8 union all select 'ClosedCount_EscrowOnly', cast(ClosedCount_EscrowOnly as varchar(10)), 9 union all select 'OpenCount_PreListingTask', cast(OpenCount_PreListingTask as varchar(10)), 10 ) c (DataType, value, so) ) d pivot ( max(value) for WeekOfTheMonth in ([1], [2], [3], [4]) )p order by seq, so 

См. SQL Fiddle with Demo .

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

 select Datatype, max(case when WeekOfTheMonth = 1 then value else '0' end) Week1, max(case when WeekOfTheMonth = 2 then value else '0' end) Week2, max(case when WeekOfTheMonth = 3 then value else '0' end) Week3, max(case when WeekOfTheMonth = 4 then value else '0' end) Week4 from ( select WeekOfTheMonth, DataType, Value, so, seq = row_number() over(partition by WeekOfTheMonth order by wwnId) from yourtable cross apply ( select 'Tenant', Tenant, 1 union all select 'ReportingDate', convert(varchar(10), ReportingDate, 120), 2 union all select 'TotalEmployeesPerBranch', cast(TotalEmployeesPerBranch as varchar(10)), 3 union all select 'TotalOpenCount', cast(TotalOpenCount as varchar(10)), 4 union all select 'TotalClosedCount', cast(TotalClosedCount as varchar(10)), 5 union all select 'OpenCount_TitleAndEscrow', cast(OpenCount_TitleAndEscrow as varchar(10)), 6 union all select 'ClosedCount_TitleAndEscrow', cast(ClosedCount_TitleAndEscrow as varchar(10)), 7 union all select 'OpenCount_EscrowOnly', cast(OpenCount_EscrowOnly as varchar(10)),8 union all select 'ClosedCount_EscrowOnly', cast(ClosedCount_EscrowOnly as varchar(10)), 9 union all select 'OpenCount_PreListingTask', cast(OpenCount_PreListingTask as varchar(10)), 10 ) c (DataType, value, so) ) d group by datatype, seq, so order by seq, so 

См. SQL Fiddle with Demo

  • Как превратить столбцы в строки
  • Горизонтальный столбец запроса
  • Как повернуть стол по горизонтали в sql-сервере
  • Объединение нескольких строк в одну строку с отдельными столбцами в SQL
  • Разверните два столбца в T-SQL
  • Применение PIVOT в T-SQL для получения строк в столбце
  • Изменение итоговой таблицы
  • SQL Pivot с несколькими значениями
  • SQL AdventureWorks рассчитывает сотрудников по полу по городам
  • Создайте более одного столбца в сводной таблице с одним оператором sql
  • SQL Server 2005 GROUP BY и COUNT запрос за каждый месяц
  • Interesting Posts

    Как получить заданное значение из хранимой процедуры в C #

    обновление одной таблицы от другой со сложными условиями

    ALTER DATABASE не удалось, так как блокировка не может быть помещена в базу данных <db_name>. Попробуйте позже

    «УСТАНОВИТЬ УРОВЕНЬ ИЗОЛЯЦИИ СТАБИЛИЗАЦИИ НЕ ПРОХОДИТ», не принимая? Или я смотрю не так?

    Медленная работа по извлечению множества вложенных объектов с использованием Entity Framework

    Получить счетчик статей о присоединении к детям

    Ошибка соединения после длительного простоя с SQL Server 2016

    как увеличить число?

    как создать базу данных в MS Visual Studio 2013?

    основная файловая группа полная

    автоматический прирост на составной первичной клавише

    Итерирование столбцов таблицы SQL Server с помощью цикла в Visual Studio SSIS

    Как рассчитать своевременную доставку, когда таблицы SQL не являются одними к одному

    Как получить столбцы первичного ключа из экземпляра TSqlObject?

    Ошибка преобразования типа данных varchar в числовой. SQL Server

    Давайте будем гением компьютера.