将行值变成列值——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
,因此您必须以某种方式替换 ID
's 和它们对应的 barn
s.
As there is no barn1..4
in your tables, you somehow have to replace the ID
's with their corresponding barn
s.
使用 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屋!