SQL 试图将行展平为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14651511/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
trying to flatten rows into columns
提问by boone
I have a group of rows in a table that have an id. I am trying to flatten it out in rows with multiple column. I am almost certain I have done this with a cte and maybe partition.
我在一个表中有一组具有 id 的行。我正在尝试将其展平成多列的行。我几乎可以肯定我已经使用 cte 和分区完成了此操作。
I have used cte's to delete duplicate data and I thought I has done something similar to what I am trying to accomplish here. I was able to come up with workable solution (listed below) but still feel like a more elegant solution should be available.
我已经使用 cte's 来删除重复的数据，我想我已经做了一些类似于我在这里尝试完成的事情。我能够想出可行的解决方案（如下所列），但仍然觉得应该有一个更优雅的解决方案。
CREATE TABLE #MyTable ( RowID int , field VARCHAR(10), value VARCHAR(10))
INSERT INTO #MyTable ( RowID, field, value ) VALUES ( 1, 'first', 'neil' )
INSERT INTO #MyTable ( RowID, field, value ) VALUES ( 2, 'first', 'bob' )
INSERT INTO #MyTable ( RowID, field, value ) VALUES ( 3, 'first', 'tom' )
INSERT INTO #MyTable ( RowID, field, value ) VALUES ( 1, 'last', 'young' )
INSERT INTO #MyTable ( RowID, field, value ) VALUES ( 2, 'last', 'dylan' )
INSERT INTO #MyTable ( RowID, field, value ) VALUES ( 3, 'last', 'petty' )
SELECT * FROM #mytable
--trying to accomplish this with cte/partition:
--尝试使用 cte/partition 来完成此操作：
SELECT rowid,
[first] = (Select value FROM #mytable where field = 'first' and rowid = t.rowid),
[last] = (Select value FROM #mytable where field = 'last' and rowid = t.rowid)
FROM #mytable t
GROUP BY rowid
回答by Taryn
This data transformation is known as a PIVOT. In SQL Server 2005+ there is a function that will perform this process. :
这种数据转换称为PIVOT. 在 SQL Server 2005+ 中，有一个函数可以执行此过程。：
select *
from
(
SELECT *
FROM mytable
) src
pivot
(
max(value)
for field in (first, last)
) piv
See SQL Fiddle with Demo.
Or you can use an aggregate function with a CASEexpression:
或者您可以使用带有CASE表达式的聚合函数：
select rowid,
max(case when field = 'first' then value end) first,
max(case when field = 'last' then value end) last
from MyTable
group by rowid
See SQL Fiddle with Demo.
You can also use multiple joins on your table:
您还可以在您的表上使用多个连接：
select t1.rowid,
t1.value first,
t2.value last
from mytable t1
left join mytable t2
on t1.rowid = t2.rowid
and t2.field = 'last'
where t1.field = 'first'
The result for all versions is the same:
所有版本的结果都是一样的：
| ROWID | FIRST | LAST |
-------------------------
| 1 | neil | young |
| 2 | bob | dylan |
| 3 | tom | petty |
