An unusual SQL question

Factual Questions
1

If you’ve been looking at database threads here for any length of time, you’re probably used to seeing questions where someone has a table laid out like this:



CREATE TABLE things
(
  thingID integer,
  type1 varchar(255),
  amount1 integer,
  ...,
  typeN varchar(255),
  amountN integer
)

The questioner pretty much always wants to write a query to convert it to something like this:



CREATE TABLE things_types
(
  thingID integer,
  type varchar(255),
  amount integer
)

It’s not pretty (or easy to maintain), but a bunch of union queries put together will get the job done.

But what about going the other way around? Is there an SQL statement that will take the second table structure and output the first one? Someone on another message board asked about this and (after giving them the appropriate warning about not replacing the good table design with a bad one) I had to recommend using an imperative language of some kind. I don’t think this can be done in straight SQL. Am I wrong? Don’t assume that every thing/type combination shows up in the second table.

2

Sometimes it seems like CASE expressions are the answers to half the SQL questions I am asked at work.



select
  'TYPE1' as type1,
  case type when 'TYPE1' then amount else null end as amount1,
  'TYPE2' as type2,
  case type when 'TYPE2' then amount else null end as amount2
  /* etc. */
from things_types ;

P.S. “pivot table SQL” would be a good search term for this kind of query

3

I think this is what you are asking for, but is horrible. Self join to thing_types with N aliases.


select a.THINGID ida, substr(a.TYPE,1,12) atype,
       b.THINGID idb, substr(b.TYPE,1,12) btype,
       c.THINGID idc, substr(c.TYPE,1,12) ctype
from things_types a, things_types b, things_types c
where a.THINGID = 1
and b.THINGID = 2
and c.THINGID = 3

Data:


select * from things_types;

  THINGID TYPE           AMOUNT
--------- -------------- ---------
        1 type one       42
        2 type two       0
        3 type three     777

Result:


      IDA ATYPE              IDB BTYPE              IDC CTYPE
--------- ------------ --------- ------------ --------- ------------
        1 type one             2 type two             3 type three

Yuk. And naturally you have to re-write every time N changes.

4

Yep, as Arnold Winkelried said, this is a classic pivot query. You shouldn’t need a self-join for every type; one run through the table is good enough. It is true that you need to know every type ahead of time tho, and that every time a new type is added, the query must be updated.

5

Are pivot table queries part of the SQL standard? I’m sure that they work, I’m just curious.

6

ANSI SQL92 standard


6.9  <case expression>

         Function

         Specify a conditional value.

         Format

         <case expression> ::=
                <case abbreviation>
              | <case specification>

         <case abbreviation> ::=
                NULLIF <left paren> <value expression> <comma>
                      <value expression> <right paren>
              | COALESCE <left paren> <value expression>
                      { <comma> <value expression> }... <right paren>

         <case specification> ::=
                <simple case>
              | <searched case>

         <simple case> ::=
              CASE <case operand>
                <simple when clause>...
                [ <else clause> ]
              END

         <searched case> ::=
              CASE
                <searched when clause>...
                [ <else clause> ]
              END

         <simple when clause> ::= WHEN <when operand> THEN <result>

         <searched when clause> ::= WHEN <search condition> THEN <result>

         <else clause> ::= ELSE <result>

         <case operand> ::= <value expression>

         <when operand> ::= <value expression>

         <result> ::= <result expression> | NULL

         <result expression> ::= <value expression>
7

That type is, and it’s supported by every major database system that I’m familiar with. Some vendor-specific implementations have better options, eg. Microsoft SQL Server 2005’s PIVOT function (newer versions of Oracle have something like this, too). So check your product documentation!

8

Don’t forget to group on thingID when implementing Arnold Winkelreid’s solution, or you’ll end up with the same number of rows, but with additional columns, most of which aren’t populated.