Pivot/Unpivot function with multiple aggregates?

• wtolbert

  SSC Enthusiast

  Points: 130

  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)

• Dwain Camps

  SSC Guru

  Points: 86978

  More actions

  May 17, 2013 at 12:43 am

  #1616056

  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