没有聚合的简单(?)PIVOT [英] simple(?) PIVOT without an aggregate

问题描述

支点，伙计...我只是想念它.也许是因为我没有做汇总.哎呀，也许枢轴不是做到这一点的方法.感觉应该很简单，但这让我感到困惑.

Pivots, man...I'm just missing it. Maybe it's because I'm not doing an aggregate. Heck, maybe a pivot isn't the way to do this. It feels like it should be simple, but it's got me stumped.

比方说我有这个:

SELECT col1
FROM tbl1

col1
====
414
589

如何将这两条记录取回:

How can I get these two records back as:

fauxfield1  fauxfield2
==========  ==========
414         589

出于这个问题的目的而提出的警告

Couple of caveats for the purposes of this question

• 我从不要取回多于两个记录
• 我总是会取回整数，但是我不知道它们会是什么.

• I'm never going to get back more than two records
• I'm always going to get back integers, but I don't know what they will be.

推荐答案

您可以实现PIVOT运算符:

select [1] as field1,
  [2] as field2
from
(
  select col1, row_number() Over(order by col1) rn
  from yourtable
) src
pivot
(
  max(col1)
  for rn in ([1], [2])
) piv

请参见带有演示的SQL提琴

这篇关于没有聚合的简单(?)PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！