I've seen a lot of "Pivot, No Agg" posts, but all of them seem to involve some pretty simple data to pivot, so the solutions work fairly well and easily. But how about when the data isn't as simple?
I'd like to turn this:
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
into this:
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
I've tried several methods of pivoting, and none of them seem to do the trick, but if anyone out there knows a way to do it, that'd be fantastic!
Thanks ahead of time!
UPDATE
This works beautifully! (initial variable declarations are for the where clause toward the end)
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;
and results in:
Datatype Week1 Week2 Week3 Week4 -------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- Tenant King King King King 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 Tenant Pierce Pierce Pierce Pierce 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
Thanks tons for the answer!
You'll need to UNPIVOT all those columns first, then convert your Weeks into new columns. But in order to UNPIVOT the data, you'll have to convert all of the data types to be the same.
Since you are using SQL Server 2008, you can use CROSS APPLY to unpivot. The basic syntax will be:
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);
See SQL Fiddle with Demo. Then you'd apply the PIVOT to your Weeks:
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
See SQL Fiddle with Demo.
Or you can use an aggregate function to create the new columns:
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments