sql – 试图将行变平成列

我有一组具有id的表.我试图用多列排列成一行.我几乎肯定我已经做了一个cte,也许分区.

我使用cte删除重复的数据,我以为我已经做了类似于我要在这里完成的事情.我能够提出可行的解决方案(如下所列),但仍然觉得应该有更优雅的解决方案.

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,value ) VALUES  ( 2,'bob'  )

INSERT INTO #MyTable ( RowID,value ) VALUES  ( 3,'tom'  )

INSERT INTO #MyTable ( RowID,'last','young' )

INSERT INTO #MyTable ( RowID,'dylan' )

INSERT INTO #MyTable ( RowID,'petty' )

SELECT * FROM #mytable

– 用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中有一个函数将执行此过程. ：

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

见SQL Fiddle with Demo.

或者您可以使用一个聚合函数与一个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

见SQL Fiddle with Demo.

您还可以在表格上使用多个连接：

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 Fiddle with Demo

所有版本的结果是一样的：

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

sql – 试图将行变平成列

解决方法

猜你在找的MsSQL相关文章