How to make awkward pivot of sql table in SQL Server 2005?

I have to rotate a given table from an SQL Server but a normal pivot just doesn’t work (as far as i tried). So has anybody an idea how to rotate the table into the desired format?

Just to make the problem more complicated, the list of given labels can vary and it is possible that a new label name can come into at any given time.

  • PHP PDO to MS SQL Server on Ubuntu Server
  • Can I use SQL Server 2008 management studio with SQL Server 2005?
  • Omit named parameters from OleDbCommand
  • Make Procedure with SCOPE_IDENTITY()
  • how to use Loops for inserting records in sql table using vb 2015?
  • Merge and When Matched query giving an error sql server
  • Given Data

    ID |  Label          |  Numerator  |  Denominator  |  Ratio 
    ---+-----------------+-------------+---------------+--------
    1  |  LabelNameOne   |  41         |  10           |  4,1   
    1  |  LabelNameTwo   |  0          |  0            |  0     
    1  |  LabelNameThree |  21         |  10           |  2,1   
    1  |  LabelNameFour  |  15         |  10           |  1,5   
    2  |  LabelNameOne   |  19         |  19           |  1     
    2  |  LabelNameTwo   |  0          |  0            |  0     
    2  |  LabelNameThree |  15         |  16           |  0,9375
    2  |  LabelNameFive  |  19         |  19           |  1     
    2  |  LabelNameSix   |  17         |  17           |  1     
    3  |  LabelNameOne   |  12         |  12           |  1     
    3  |  LabelNameTwo   |  0          |  0            |  0     
    3  |  LabelNameThree |  11         |  12           |  0,9167
    3  |  LabelNameFour  |  12         |  12           |  1     
    3  |  LabelNameSix   |  0          |  1            |  0     
    

    Wanted result

    ID | ValueType   | LabelNameOne | LabelNameTwo | LabelNameThree | LabelNameFour | LabelNameFive | LabelNameSix
    ---+-------------+--------------+--------------+----------------+---------------+---------------+--------------
    1  | Numerator   | 41           | 0            | 21             | 15            |               |              
    1  | Denominator | 10           | 0            | 10             | 10            |               |              
    1  | Ratio       | 4,1          | 0            | 2,1            | 1,5           |               |              
    2  | Numerator   | 19           | 0            | 15             |               | 19            | 17           
    2  | Denominator | 19           | 0            | 16             |               | 19            | 17           
    2  | Ratio       | 1            | 0            | 0,9375         |               | 1             | 1            
    3  | Numerator   | 12           | 0            | 11             | 12            |               | 0            
    3  | Denominator | 12           | 0            | 12             | 12            |               | 1            
    3  | Ratio       | 1            | 0            | 0,9167         | 1             |               | 0            
    

  • How can i adjust this T-SQL query so that it returns one table rather than two? :)
  • sync two local and remote sql server database
  • SQL query doesn't execute on MSSQL
  • SQL Use grouped values to use in a subquery + “where column in())”
  • How can I select the latest event for a given user in Microsoft SQL Server 2008?
  • combing two rows of data in Sql
  • 3 Solutions collect form web for “How to make awkward pivot of sql table in SQL Server 2005?”

    This should sort you out. It’s really an UNPIVOT and a PIVOT. Note that you have to conform your data because the UNPIVOT puts all the data in the same column.

    Note that I had to recreate/repopulate the table variable in the inner dynamic SQL – typically this is not necessary when dealing with a permanent table.

    SET NOCOUNT ON ;
    
    DECLARE @pivot_cols AS varchar(max) ;
    DECLARE @src AS TABLE
        (
         ID int NOT NULL
        ,Label varchar(14) NOT NULL
        ,Numerator int NOT NULL
        ,Denominator int NOT NULL
        ,Ratio decimal(5, 4) NOT NULL
        ) ;
    
    DECLARE @label_order AS TABLE
        (
         Label varchar(14) NOT NULL
        ,Sort int NOT NULL
        )
    
    INSERT  INTO @src
    VALUES  (1, 'LabelNameOne', 41, 10, 4.1) ;
    INSERT  INTO @src
    VALUES  (1, 'LabelNameTwo', 0, 0, 0) ;
    INSERT  INTO @src
    VALUES  (1, 'LabelNameThree', 21, 10, 2.1) ;
    INSERT  INTO @src
    VALUES  (1, 'LabelNameFour', 15, 10, 1.5) ;
    INSERT  INTO @src
    VALUES  (2, 'LabelNameOne', 19, 19, 1) ;
    INSERT  INTO @src
    VALUES  (2, 'LabelNameTwo', 0, 0, 0) ;
    INSERT  INTO @src
    VALUES  (2, 'LabelNameThree', 15, 16, 0.9375) ;
    INSERT  INTO @src
    VALUES  (2, 'LabelNameFive', 19, 19, 1) ;
    INSERT  INTO @src
    VALUES  (2, 'LabelNameSix', 17, 17, 1) ;
    INSERT  INTO @src
    VALUES  (3, 'LabelNameOne', 12, 12, 1) ;
    INSERT  INTO @src
    VALUES  (3, 'LabelNameTwo', 0, 0, 0) ;
    INSERT  INTO @src
    VALUES  (3, 'LabelNameThree', 11, 12, 0.9167) ;
    INSERT  INTO @src
    VALUES  (3, 'LabelNameFour', 12, 12, 1) ;
    INSERT  INTO @src
    VALUES  (3, 'LabelNameSix', 0, 1, 0) ;
    
    INSERT  INTO @label_order
    VALUES  ('LabelNameOne', 1) ;
    INSERT  INTO @label_order
    VALUES  ('LabelNameTwo', 2) ;
    INSERT  INTO @label_order
    VALUES  ('LabelNameThree', 3) ;
    INSERT  INTO @label_order
    VALUES  ('LabelNameFour', 4) ;
    INSERT  INTO @label_order
    VALUES  ('LabelNameFive', 5) ;
    INSERT  INTO @label_order
    VALUES  ('LabelNameSix', 6) ;
    
    WITH    Labels
              AS (
                  SELECT  DISTINCT
                            src.Label
                           ,ISNULL(label_order.Sort, 0) AS Sort
                  FROM      @src AS src
                  LEFT JOIN @label_order AS label_order
                            ON src.label = label_order.label
                 )
        SELECT  @pivot_cols = COALESCE(@pivot_cols + ',', '') + QUOTENAME(Label, '[')
        FROM    Labels
        ORDER BY Sort
               ,Label ;
    
    DECLARE @template AS varchar(max) ;
    
    SET @template = '
    DECLARE @src AS TABLE
        (
         ID int NOT NULL
        ,Label varchar(14) NOT NULL
        ,Numerator int NOT NULL
        ,Denominator int NOT NULL
        ,Ratio decimal(5, 4) NOT NULL
        ) ;
    
    INSERT  INTO @src
    VALUES  (1, ''LabelNameOne'', 41, 10, 4.1) ;
    INSERT  INTO @src
    VALUES  (1, ''LabelNameTwo'', 0, 0, 0) ;
    INSERT  INTO @src
    VALUES  (1, ''LabelNameThree'', 21, 10, 2.1) ;
    INSERT  INTO @src
    VALUES  (1, ''LabelNameFour'', 15, 10, 1.5) ;
    INSERT  INTO @src
    VALUES  (2, ''LabelNameOne'', 19, 19, 1) ;
    INSERT  INTO @src
    VALUES  (2, ''LabelNameTwo'', 0, 0, 0) ;
    INSERT  INTO @src
    VALUES  (2, ''LabelNameThree'', 15, 16, 0.9375) ;
    INSERT  INTO @src
    VALUES  (2, ''LabelNameFive'', 19, 19, 1) ;
    INSERT  INTO @src
    VALUES  (2, ''LabelNameSix'', 17, 17, 1) ;
    INSERT  INTO @src
    VALUES  (3, ''LabelNameOne'', 12, 12, 1) ;
    INSERT  INTO @src
    VALUES  (3, ''LabelNameTwo'', 0, 0, 0) ;
    INSERT  INTO @src
    VALUES  (3, ''LabelNameThree'', 11, 12, 0.9167) ;
    INSERT  INTO @src
    VALUES  (3, ''LabelNameFour'', 12, 12, 1) ;
    INSERT  INTO @src
    VALUES  (3, ''LabelNameSix'', 0, 1, 0) ;
    
    WITH    src_conformed
              AS (
                  SELECT    ID
                           ,Label
                           ,CAST (Numerator AS decimal(10, 4)) AS Numerator
                           ,CAST (Denominator AS decimal(10, 4)) AS Denominator
                           ,CAST (Ratio AS decimal(10, 4)) AS Ratio
                  FROM      @src
                 ),
            UNPIVOTED
              AS (
                  SELECT    *
                  FROM      src_conformed UNPIVOT ( Val FOR Col IN (Numerator, Denominator, Ratio) ) AS unpvt
                 )
        SELECT  *
        FROM    UNPIVOTED PIVOT ( SUM(Val) FOR Label IN ({@pivot_cols}) ) AS pvt
        ORDER BY ID
               ,Col ;' ;
    
    SET @template = REPLACE(@template, '{@pivot_cols}', @pivot_cols) ;
    
    EXEC (@template) ;
    
    select
    id,
    'Numerator' as ValueType,
    case when label = labelNameOne then Numerator else 0 end as LabelNameOne,
    case when label = labelNameTwo then Numerator else 0 end as LabelNameTwo,
    case when label = labelNameTree then Numerator else 0 end as LabelNameTree,
    case when label = labelNameFour then Numerator else 0 end as LabelNameFour,
    case when label = labelNameFive then Numerator else 0 end as LabelNameFive,
    case when label = labelNameSix then Numerator else 0 end as LabelNameSix
    
    union All
    

    … similar query with Denominator …

    union all
    

    … similar query with Ratio…

    What you seek is dynamic cross tab. The short answer is that it cannot be done in T-SQL without some fugly dynamic SQL. The Hoyle answer is that you should pivot the data in a reporting tool or in the middle tier.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.