# An unusual SQL question

**URL:** https://boards.straightdope.com/t/an-unusual-sql-question/442678
**Category:** Factual Questions
**Created:** [March 25, 2008, 2:18pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678 "2008-03-25T14:18:53Z")
**Posts on this page:** 8
**Page:** 1

<div class="post-metadata">

### Author: ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [March 25, 2008, 2:18pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/1 "2008-03-25T14:18:53Z")

</div>

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:

```auto

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:

```auto

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.

---

<div class="post-metadata">

### Author: ![Arnold\_Winkelried](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [March 25, 2008, 2:44pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/2 "2008-03-25T14:44:56Z")

</div>

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

```auto

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

---

<div class="post-metadata">

### Author: ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)
#### Post date: [March 25, 2008, 4:35pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/3 "2008-03-25T16:35:28Z")

</div>

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

```auto

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:

```auto

select * from things_types;

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

```

Result:

```auto

      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.

---

<div class="post-metadata">

### Author: ![arseNal](https://avatars.discourse-cdn.com/v4/letter/a/ecae2f/32.png) [@arseNal](https://boards.straightdope.com/u/arseNal)
#### Post date: [March 25, 2008, 4:43pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/4 "2008-03-25T16:43:49Z")

</div>

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.

---

<div class="post-metadata">

### Author: ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [March 25, 2008, 7:32pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/5 "2008-03-25T19:32:02Z")

</div>

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

---

<div class="post-metadata">

### Author: ![Arnold\_Winkelried](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [March 25, 2008, 8:10pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/6 "2008-03-25T20:10:15Z")

</div>

[ANSI SQL92 standard](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)

```auto

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>

```

---

<div class="post-metadata">

### Author: ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [March 25, 2008, 8:58pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/7 "2008-03-25T20:58:00Z")

</div>

[QUOTE=ultrafilter]  
Are pivot table queries part of the SQL standard? I’m sure that they work, I’m just curious.  
[/QUOTE]

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!

---

<div class="post-metadata">

### Author: ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)
#### Post date: [March 25, 2008, 11:17pm UTC](https://boards.straightdope.com/t/an-unusual-sql-question/442678/8 "2008-03-25T23:17:59Z")

</div>

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.