Using PIVOT in SQL Server

How can i convert below using PIVOT

alt text http://img689.imageshack.us/img689/4445/actualb.jpg

  • Cannot insert into table with types in SQL Server
  • How to treat negative values when performing a sum
  • SQL How to insert a new row in the middle of the table
  • Get Products By Minimum Duration Between StartDate and EndDate?
  • Passing a checkbox value to a SQL Server stored procedure
  • how to build sql server 2008 datetime object and insert it using perl DBI
  • as

    alt text http://img710.imageshack.us/img710/3482/pivot.jpg

    Thanks

  • How to Insert Multiple Row from one Column
  • NOLOCK vs. Transaction Isolation Level
  • On creating a table column of type “float with precision” column gets created as “real” type in SQL server
  • Last working day for n Months ago SQL
  • How output data from database to table?
  • Nested Repeaters and SqlDataSource Parameters
  • 2 Solutions collect form web for “Using PIVOT in SQL Server”

    It`s a bit messy, but here it is.

    First of all I use the rank() function to know the position (1,2 or 3) of the ErrorField.
    With all that, I use that number for the Pivot.
    You need two pivots and a join between them.

    WITH AuxTable (Data_Error_Key, ErrorField, ErrorValue, NumeroError) 
    AS
    (
    SELECT Data_Error_Key, ErrorField, ErrorValue, RANK() OVER (PARTITION BY Data_Error_Key ORDER BY Data_Error_Key, ErrorField)
    FROM dbo.TempTable
    )
    SELECT TablaErrorField.Data_Error_Key, ErrorField1, ErrorValue1,ErrorField2,ErrorValue2,  ErrorField3,
          ErrorValue3
    FROM 
    (
        SELECT Data_Error_Key, [1] as ErrorField1, [2] as ErrorField2, [3] as ErrorField3
        FROM (
        SELECT Data_Error_Key,NumeroError, ErrorField
        FROM AuxTable) P
        PIVOT
        (
        MAX (ErrorField)
        FOR NumeroError IN ([1], [2], [3])
        ) AS pvt) As TablaErrorField
    INNER JOIN 
    (
        SELECT Data_Error_Key, [1] as ErrorValue1, [2] as ErrorValue2, [3] as ErrorValue3
        FROM (
        SELECT Data_Error_Key,NumeroError, ErrorValue
        FROM AuxTable) P
        PIVOT
        (
        MAX (ErrorValue)
        FOR NumeroError IN ([1], [2], [3])
        ) AS pvt) as TablaErrorValue
    ON TablaErrorField.Data_Error_Key= TablaErrorValue.Data_Error_Key
    

    All this assuming you need only 3 pairs of ErrorField/ErrorValue. Otherwise, you should take a look at my response in this question.

    I don’t know if you can do that with the SQL Server PIVOT function. That function always assumes and requires some kind of an aggregate function (COUNT, AVG) on a numeric value – you can’t just transpose rows to columns.

    In your case, if you know your error fields in advance, you could do something like this:

    SELECT 
       ErrorKey,
       [Field1] AS Field1, [Field2] AS Field2, [Field10] AS Field10, 
       [Field11] AS Field11, [Field13] as Field13, [Field14] as Field14, 
       [Field15] as Field15, [Field21] as field21
    FROM 
       (SELECT ErrorKey, ErrorField, ErrorValue
        FROM Errors) e
    PIVOT
    (
    COUNT (ErrorValue)
    FOR ErrorField IN
        ( [Field1], [Field2], [Field10], [Field11], [Field13], 
          [Field14], [Field15], [Field21] )
    ) AS pvt
    ORDER BY pvt.ErrorKey
    

    This would produce an output something like this:

    ErrorKey    Field1  Field2  Field10 Field11 Field13 Field14 Field15 field21
      1           1       1        1      0        0      0        0       0
      2           0       0        0      1        1      1        0       0
      3           0       0        0      0        0      0        1       1
    

    This would give you the count of error in a particular field, for each error key.

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