simple(?) PIVOT without an aggregate

Question

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.

Let's say I've got this:

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.

Answer 1

If you're only ever going to have 2 values, you could do it like this

select
    (select top(1) col1 from tbl1 order by col1) fauxfield1,
    (select top(1) col1 from tbl1 order by col1 desc) fauxfield2;

What I don't understand however is why there is a need to avoid aggregates? Have you found some crippled version of SQL Server? The normal query would be

select min(col1) fauxfield1, max(col1) fauxfield2
  from tbl1;

Answer 2

You can implement the PIVOT operator:

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

See SQL Fiddle with Demo

Answer 3

If you know you're only getting two, why not this:

SELECT 
    MIN(col1) ff1
    , CASE MAX(col1) 
        WHEN MIN(col1) THEN NULL
        ELSE MAX(col1)
      END ff2
FROM 
    tbl1;

This only shows a second value if there are two.