none
Pivot table not returning values

    Вопрос

  • 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

    9 июня 2012 г. 17:14

Ответы

Все ответы