试图将行平整为列 [英] trying to flatten rows into columns

问题描述

我在表中有一组具有ID的行。我试图将其平铺成多列的行。我几乎可以肯定我已经使用cte以及可能的分区来做到这一点。

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.

我使用过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.

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

-试试要使用cte / partition完成此操作：

--trying to accomplish this with 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

推荐答案

此数据转换称为 PIVOT 。在SQL Server 2005+中，有一个函数可以执行此过程。 ：

This data transformation is known as a PIVOT. In SQL Server 2005+ there is a function that will perform this process. :

select *
from
(
  SELECT * 
  FROM mytable
) src
pivot
(
  max(value)
  for field in (first, last)
) piv

请参见带演示的SQL Fiddle 。

或者您可以将聚合函数与 CASE 表达式一起使用：

Or you can use an aggregate function with a CASE expression:

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

请参见< a href = http://sqlfiddle.com/#!3/cf727/6>带演示的SQL提琴。

您也可以使用多个联接到您的表上：

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'

请参见带演示的SQL提琴

所有版本的结果都相同：

The result for all versions is the same:

| ROWID | FIRST |  LAST |
-------------------------
|     1 |  neil | young |
|     2 |   bob | dylan |
|     3 |   tom | petty |

这篇关于试图将行平整为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！