将行值转换为列值-SQL PIVOT [英] Making row values into column values -- SQL PIVOT

问题描述

SQL从表中返回以下结果:CowTracking

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

我正在尝试使用SQL中的PIVOT获得以下结果

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

注意:barn_id是一个varchar.将无法更改数据类型.

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?

我什至需要使用PIVOT吗?

Do I even need to use PIVOT?

推荐答案

由于表中没有barn1..4，因此您必须以某种方式用相应的barn替换ID.

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

使用PIVOT的一种解决方案可能是这样的

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                 

使用CASE和GROUP BY的另一种解决方案可能是

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

，但从本质上讲，这全部归结为将ID硬编码为barn.

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

修改

如果您总是返回固定数量的记录，并且使用SQL Server，则可以通过

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

• 为每个结果添加ROW_NUMBER
• 在此预先知道的号码上旋转

SQL语句

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

测试脚本

;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

这篇关于将行值转换为列值-SQL PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！