Pivot table not returning values

• 9 июня 2012 г. 17:14

   

   
  

  0

  I'm creating a pivot in a stored procedure the same way i have don in the past (which has always worked), but now it returns NULL instead of the summed values.  To make sure I'm doing things consistently, I created a temp table to insert the initail data, then can use the same code to pivot the data.  for example:

  create table #PivotData (RowLabel nvarchar(200), Value float, HeaderLabel nvarchar(200) )

  then after inserting data into #PivotData, I can run this code which normaly will work:

  select * from #PivotData
  
  PIVOT (
  SUM(Value)
  FOR [HeaderLabel] IN (
  @ColumnList)
  ) PivotTable

  Here's how to demonstrate my problem:
  

  create table #PivotData (RowLabel nvarchar(200), Value float, HeaderLabel nvarchar(200) )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  
  values ('Row 3', 0.1, 'extend   assembly' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 3', NULL, 'evaluate   line' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', 0.23, 'Accommodate   recommendations' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 3', 0.4, 'acquire   agreement ' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', NULL, 'Officiate   administration' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 3', 0.42, 'Accommodate   recommendations' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', 0.1, 'access   agreement ' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 3', 0.2, 'access   agreement ' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', NULL, 'evaluate   line' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', 0.007, 'extend   assembly' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', NULL, 'evaluate   line' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', 0.27, 'Control   Flow' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', 0.3456, 'acquire   agreement ' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 3', 0.2, 'Control   Flow' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', 0.3, 'access   agreement ' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', 0.19, 'Control   Flow' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', NULL, 'Officiate   administration' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', 0.15, 'Accommodate   recommendations' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 3', NULL, 'Officiate   administration' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 1', 0.32, 'extend   assembly' )
  insert into #PivotData (RowLabel, Value, HeaderLabel)
  values ('Row 2', 0.2, 'acquire   agreement ' )
  
  
  select * from #PivotData
  
  PIVOT (
  SUM(Value)
  FOR [HeaderLabel] IN (
  [access agreement ],[Accommodate recommendations],[acquire agreement ],[Control Flow],[evaluate line],[extend assembly],[Officiate administration]  
  )
  ) PivotTable

  and the resulting data will look like this:

  RowLabel access agreement  Accommodate recommendations acquire agreement  Control Flow evaluate line extend assembly Officiate administration
  Row 1 NULL NULL NULL NULL NULL NULL NULL
  Row 2 NULL NULL NULL NULL NULL NULL NULL
  Row 3 NULL NULL NULL NULL NULL NULL NULL

  Can someone please explain why Im getting NULLs and not the numeric values?

  Thank you.

  ---

  moondaddy

  • Ответить
  • Цитировать