Pivot/Unpivot function with multiple aggregates?

Question

Post reply

Pivot/Unpivot function with multiple aggregates?

wtolbert

SSC Enthusiast

Points: 100
More actions
May 16, 2013 at 1:37 pm

#277691

I'm a newbie to the forum and I was hoping that I could get some help with my script. I have a simple query that I would like to transpose the rows to columns but I'm unsure of how to do that having multiple columns. My script is as follows:

SELECT dt.YEAR [caucasian_enrollment] = SUM(caucasian_enrollment) ,[black_or_african_american_enrollment] = SUM(black_or_african_american_enrollment) ,[asian_enrollment] = SUM(asian_enrollment) ,[hispanic_enrollment] = SUM(hispanic_enrollment) ,[native_hawaiian_pacific_islander_enrollment] = SUM(native_hawaiian_pacific_islander_enrollment) ,[american_indian_or_alaskan_ative_enrollment] = SUM(american_indian_or_alaskan_ative_enrollment) ,[multiracial_enrollment] = SUM(multiracial_enrollment) ,[unknown_ethnicity_enrollment] = SUM(unknown_ethnicity_enrollment) FROM mart.dbo.fact_enrollment_school AS fes INNER JOIN dim_time AS dt ON fes.time_key = dt.time_key INNER JOIN dim_time AS dt2 ON fes.time_key = dt.time_key GROUP BY dt.year

My data currently looks like the following:

Year caucasian Black Asian....

2012 3000 1000 100

2011 4000 2000 50

2010 2500 1500 30

I would like it to look like the following:

Race 2012 % inc/dec 2011 % inc/dec 2010

Caucasian 3000 -.33 4000 .6 2500

Black 1000 -.5 2000 .25 1500

Asian 100 .5 50 .4 30

I have started my script below but cannot get it to work correctly with multiple aggregates:

WITH cte
AS ( SELECT * FROM ( SELECT [year] , --aggregates FROM mart.[dbo].[vw_salary] ) AS source PIVOT ( SUM(total_salary) FOR [year] IN ( [2012], [2011], [2010], [2009], [2008]) ) as pvt ) SELECT [2012] , ( CASE WHEN [2011] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2012] - [2011] ) / CONVERT(DECIMAL(18, 2), ( [2011] ))) * 100 END ) AS '% End Balance -/+' , [2011] , ( CASE WHEN [2010] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2011] - [2010] ) / CONVERT(DECIMAL(18, 2), ( [2010] ))) * 100 END ) AS '% End Balance -/+' , [2010] , ( CASE WHEN [2009] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2010] - [2009] ) / CONVERT(DECIMAL(18, 2), ( [2009] ))) * 100 END ) AS '% End Balance -/+' , [2009] , ( CASE WHEN [2008] <> 0 THEN CONVERT(DECIMAL(18, 2), ( [2009] - [2008] ) / CONVERT(DECIMAL(18, 2), ( [2008] ))) * 100 END ) AS '% End Balance -/+' FROM cte

Here you will find a sample of my data set. I would really appreciate your help:

CREATE TABLE [dbo].[dim_time](
[time_key] [int] IDENTITY(1,1) NOT NULL, [year] [int] NULL ), CONSTRAINT [PK_dim_time_time_key] PRIMARY KEY CLUSTERED ( [time_key] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'dim_time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dim_time' GO SET IDENTITY_INSERT [dbo].[dim_time] ON INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (1, 2007) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (2, 2008) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (3, 2009) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (4, 2010) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (5, 2011) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (6, 2012) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (7, 2013) INSERT [dbo].[dim_time] ([time_key], [year]) VALUES (8, 2006) SET IDENTITY_INSERT [dbo].[dim_time] OFF /****** Object: Table [dbo].[fact_enrollment_school] Script Date: 05/16/2013 14:00:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[fact_enrollment_school]( [fact_enrollment_school_key] [int] IDENTITY(1,1) NOT NULL, [building_key] [int] NULL, [time_key] [int] NULL, [total_enrollment] [decimal](18, 2) NULL, [asian_enrollment] [decimal](18, 2) NULL, [native_hawaiian_pacific_islander_enrollment] [decimal](18, 2) NULL, [black_or_african_american_enrollment] [decimal](18, 2) NULL, [hispanic_enrollment] [decimal](18, 2) NULL, [caucasian_enrollment] [decimal](18, 2) NULL, [american_indian_or_alaskan_ative_enrollment] [decimal](18, 2) NULL, [multiracial_enrollment] [decimal](18, 2) NULL, [unknown_ethnicity_enrollment] [decimal](18, 2) NULL, [male_enrollment] [decimal](18, 2) NULL, [female_enrollment] [decimal](18, 2) NULL, [unknown_gender_enrollment] [decimal](18, 2) NULL, [low_income_enrollment] [decimal](18, 2) NULL, [free_reduced_lunch_count] [decimal](18, 2) NULL, [free_lunch_count] [decimal](18, 2) NULL, [reduced_lunch_count] [int] NULL, [last_create_date] [datetime] NULL, [last_update_date] [datetime] NULL, [student_with_a_disability] [decimal](18, 2) NULL, CONSTRAINT [PK_fact_enrollment_school] PRIMARY KEY CLUSTERED ( [fact_enrollment_school_key] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[fact_enrollment_school] ON INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (1, 9, 2, CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (2, 9, 3, CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (3, 12, 2, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (4, 15, 2, CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (5, 15, 3, CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (6, 26, 6, CAST(139.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(124.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(77.00 AS Decimal(18, 2)), CAST(62.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(83.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (7, 26, 4, CAST(168.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(160.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(84.00 AS Decimal(18, 2)), CAST(84.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(80.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (8, 26, 5, CAST(149.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(136.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(79.00 AS Decimal(18, 2)), CAST(70.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(84.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (9, 26, 3, CAST(190.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(183.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(83.00 AS Decimal(18, 2)), CAST(107.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(83.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (10, 26, 2, CAST(183.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(178.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(86.00 AS Decimal(18, 2)), CAST(97.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(80.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (11, 32, 2, CAST(8.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(8.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (12, 35, 4, CAST(30.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(30.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(14.00 AS Decimal(18, 2)), CAST(16.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (13, 35, 5, CAST(19.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(14.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(8.00 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (14, 35, 6, CAST(29.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(26.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(11.00 AS Decimal(18, 2)), CAST(18.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (15, 41, 2, CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (16, 41, 4, CAST(6.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(6.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (17, 41, 3, CAST(12.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(12.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(10.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (18, 49, 2, CAST(166.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(166.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(81.00 AS Decimal(18, 2)), CAST(85.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(57.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (19, 49, 4, CAST(194.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(189.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(95.00 AS Decimal(18, 2)), CAST(99.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(76.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (20, 49, 3, CAST(191.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(184.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(99.00 AS Decimal(18, 2)), CAST(92.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(64.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (21, 52, 2, CAST(264.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(35.00 AS Decimal(18, 2)), CAST(222.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(148.00 AS Decimal(18, 2)), CAST(116.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (22, 55, 5, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (23, 55, 6, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (24, 55, 3, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (25, 55, 4, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (26, 58, 2, CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(4.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (27, 61, 5, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (28, 61, 6, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (29, 61, 4, CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (30, 61, 2, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (31, 72, 5, CAST(11.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(9.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (32, 72, 4, CAST(22.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(14.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(17.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL) INSERT [dbo].[fact_enrollment_school] ([fact_enrollment_school_key], [building_key], [time_key], [total_enrollment], [asian_enrollment], [native_hawaiian_pacific_islander_enrollment], [black_or_african_american_enrollment], [hispanic_enrollment], [caucasian_enrollment], [american_indian_or_alaskan_ative_enrollment], [multiracial_enrollment], [unknown_ethnicity_enrollment], [male_enrollment], [female_enrollment], [unknown_gender_enrollment], [low_income_enrollment], [free_reduced_lunch_count], [free_lunch_count], [reduced_lunch_count], [last_create_date], [last_update_date], [student_with_a_disability]) VALUES (33, 72, 3, CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0, CAST(0x0000A1BF00A19522 AS DateTime), CAST(0x0000A1BF00A19522 AS DateTime), NULL)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86913 More actions · Answer 1

This should give you the UNPIVOT/PIVOT you need but perhaps you'll need to adjust the percentages.



DECLARE @Data TABLE ([Year] INT, caucasian INT, Black INT, Asian INT)

INSERT INTO @Data

SELECT 2012,3000,1000,100

UNION ALL SELECT 2011,4000,2000,50

UNION ALL SELECT 2010,2500,1500,30

--Race 2012 % inc/dec 2011 % inc/dec 2010

--Caucasian 3000 -.33 4000 .6 2500

--Black 1000 -.5 2000 .25 1500

--Asian 100 .5 50 .4 30

SELECT Race

    ,[2012]=MAX(CASE WHEN a.[Year]=2012 THEN [Count] END)

    ,[% Inc Dec]=(MAX(CAST(CASE WHEN a.[Year]=2011 THEN [Count] END AS FLOAT))-

        MAX(CAST(CASE WHEN a.[Year]=2012 THEN [Count] END AS FLOAT)))/

        MAX(CAST(CASE WHEN a.[Year]=2011 THEN [Count] END AS FLOAT))

    ,[2011]=MAX(CASE WHEN a.[Year]=2011 THEN [Count] END)

    ,[% Inc Dec]=(MAX(CAST(CASE WHEN a.[Year]=2010 THEN [Count] END AS FLOAT))-

        MAX(CAST(CASE WHEN a.[Year]=2011 THEN [Count] END AS FLOAT)))/

        MAX(CAST(CASE WHEN a.[Year]=2010 THEN [Count] END AS FLOAT))

    ,[2010]=MAX(CASE WHEN a.[Year]=2010 THEN [Count] END)

FROM @Data

CROSS APPLY (

    VALUES ('Caucasian', caucasian, [Year])

        ,('Black', Black, [Year])

        ,('Asian', Asian, [Year])) a (Race, [Count], [Year])

GROUP BY Race

Edit: The first article linked to my signature explains the CROSS APPLY VALUES approach to UNPIVOT if you haven't seen it before.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St