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.

score 2 · Answer 1 · answered Dec 06 '12 at 19:10

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;

score 1 · Answer 2 · answered Dec 06 '12 at 19:11

1

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.

answered Dec 06 '12 at 19:11

JAQFrost

1,431
8
8

score 1 · Accepted Answer · answered Dec 06 '12 at 19:13

1

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

answered Dec 06 '12 at 19:13

Taryn

242,637
56
362
405

Solid Gold! Took me a bit to puzzle out what you were doing with the row_number. Thanks so much! – astraljack Dec 06 '12 at 20:41
Follow up - How do I do this in SQL 2000 without the ranking functions (like row_number)? – astraljack Dec 07 '12 at 01:57
Are you using SQL Server 2000? If so, then there is no `PIVOT` function or windowing functions like `row_number()` and you will have to use one of the other answers to get your result. – Taryn Dec 07 '12 at 10:20

simple(?) PIVOT without an aggregate

3 Answers3

Linked