Making row values into column values -- SQL PIVOT

Question

SQL Returns the following results from table: CowTracking

    ID  cow_id  barn_id
    --  ------  -------
    19    5       3
    20    5       2
    21    5       9
    22    5       1

I am trying to get the following results with a PIVOT in SQL

     cow_id  barn1  barn2  barn3  barn4
     ------  -----  -----  -----  -----
       5       3      2      9      1

This is the code I have so far.

    SELECT *
    FROM 
    (
        SELECT TOP 4 *
        FROM CowTracking
            WHERE cow_id = 5
    ) AS DataTable
    PIVOT
    (
        MIN(barn_id) **IDK what function to use and which column to use it on**
        FOR ID  ??<---**NOT SURE**
        IN 
        (
        [barn1], [barn2], [barn3], [barn4]
        )
    ) AS PivotTable


    ERRORS: Error converting data type nvarchar to int
            The incorrect value "barn1" is supplied in the PIVOT operator

NOTE: The barn_id is a varchar. It will not be possible to change the datatype.

I am not trying to add/multiply/aggregate or whatever. I am simply trying to move the row to a column

How would I go about doing this? Is this the correct thought process?

Do I even need to use PIVOT?

Lieven Keersmaekers · Accepted Answer · 2012-02-02 19:40:03Z

As there is no barn1..4 in your tables, you somehow have to replace the ID's with their corresponding barns.

One solution using PIVOT might be like this

SELECT  cow_id
        , [19] as [barn1]
        , [20] as [barn2]
        , [21] as [barn3]
        , [22] as [barn4]
FROM    (       
            SELECT  *
            FROM    DataTable
            PIVOT   (   MIN(barn_id)
                        FOR ID IN ([19], [20], [21], [22])
                    ) AS PivotTable
        ) q

another solution using CASE and GROUP BY could be

SELECT  cow_id
        , [barn1] = SUM(CASE WHEN ID = 19 THEN barn_id END)
        , [barn2] = SUM(CASE WHEN ID = 20 THEN barn_id END)
        , [barn3] = SUM(CASE WHEN ID = 21 THEN barn_id END)
        , [barn4] = SUM(CASE WHEN ID = 22 THEN barn_id END)
FROM    DataTable
GROUP BY
        cow_id

but in essence, this all boils down to hardcoding an ID to a barn.

Edit

If you always return a fixed number of records, and using SQL Server you might make this a bit more robust by

adding a ROW_NUMBER to each result
pivot on this upfront known number

SQL Statement

SELECT  cow_id  
        , [barn1] = SUM(CASE WHEN rn = 1 THEN barn_id END)
        , [barn2] = SUM(CASE WHEN rn = 2 THEN barn_id END)
        , [barn3] = SUM(CASE WHEN rn = 3 THEN barn_id END)
        , [barn4] = SUM(CASE WHEN rn = 4 THEN barn_id END)
FROM    (
            SELECT  cow_id
                    , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    , barn_id
            FROM    DataTable       
        ) q
GROUP BY
        cow_id

Test script

;WITH DataTable (ID, cow_id, barn_id) AS (
    SELECT * FROM (VALUES 
        (19, 5, 3)
        , (20, 5, 2)
        , (21, 5, 9)
        , (22, 5, 1)
    ) AS q (a, b, c)        
)
SELECT  cow_id  
        , [barn1] = SUM(CASE WHEN rn = 1 THEN barn_id END)
        , [barn2] = SUM(CASE WHEN rn = 2 THEN barn_id END)
        , [barn3] = SUM(CASE WHEN rn = 3 THEN barn_id END)
        , [barn4] = SUM(CASE WHEN rn = 4 THEN barn_id END)
FROM    (
            SELECT  cow_id
                    , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    , barn_id
            FROM    DataTable       
        ) q
GROUP BY
        cow_id

your first suggestion is spot on. at first i was worried because the ID would always change. but then i declared a new column and had it set so that it would always be 1 - 4. then i was able to accomplish it. thanks mate, much appreciated. took me a little bit to get my head around this concept

Collectives™ on Stack Overflow

Making row values into column values -- SQL PIVOT

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related