我有一组具有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
或者您可以使用一个聚合函数与一个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
您还可以在表格上使用多个连接：
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'
所有版本的结果是一样的：
| ROWID | FIRST | LAST | ------------------------- | 1 | neil | young | | 2 | bob | dylan | | 3 | tom | petty |
