sql dynamic table creation with variable columns

Question

I have a table that actually already is neatly structured, but I need to change the output format. The initial table is of the format

Class Type Value
c1      t1    1
c2      t1    2
c3      
c1      t2    3
c2      t2    2
c3      t2    5
c2      t3    1
c3      t4    6
c1      t1    9
c2      t1    2

I need to dynamically create a table that has the different classes as rows, the types as columns and sums the values. the types are not fixed, so I can't build a static table. How can I dynamically create a table that looks like

class    t1    t2    t3    t4
c1       10     3     0     0
c2        4     2     1     0
c3        0     5     0     6

Is there a way to do that rather easily with sql?

As I was pointed to pivot, I had a look into it just to learn that mysql is not supporting this. what is the easiest and most straightforward way in sql to mimic pivot?

is the number types fixed to 4 items t1,t2,t3 and t4. if yes its easy to use a pivot — Lin
– Lin, Commented May 20, 2014 at 7:06
Since the types are not fixed, you will need a PIVOT or CROSSTAB syntax. Not all RDBMS support this type of feature. Which RDBMS are you using? — Frazz
– Frazz, Commented May 20, 2014 at 7:06
mysql, which unfortunately does not support pivot as I learned — chrise
– chrise, Commented May 20, 2014 at 10:37

Dmitrii Bychenko · Accepted Answer · 2014-05-20 07:10:51Z

1

If there're 4 types (i.e. t1, t2, t3, t4) only, try case construction, something like that:

  select class,
         sum(case 
               when Type = 't1' then
                 value
               else
                 0
             end) as t1, 
         sum(case 
               when Type = 't2' then
                 value
               else
                 0
             end) as t2,
         sum(case 
               when Type = 't3' then
                 value
               else
                 0
             end) as t3,
         sum(case 
               when Type = 't4' then
                 value
               else
                 0
             end) as t4     
    from MyTable
group by class

In general case (if t1..tn are not fixed) you have to pivot

edited May 20, 2014 at 7:10

answered May 20, 2014 at 7:03

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chrise Over a year ago

t'2 are not fixed. Thanks for pointing out pivot. I was not aware of this feature. a quick google search makes it look like pivot could be my solution

Raging Bull · Accepted Answer · 2014-05-20 07:12:22Z

1

If the number types are fixed to 4 (t1,t2,t3 and t4). We can do this without PIVOT:

Try this:

SELECT class,
      COALESCE(SUM(CASE WHEN Type='t1' THEN Value END),0) AS t1,
      COALESCE(SUM(CASE WHEN Type='t2' THEN Value END),0) AS t2,
      COALESCE(SUM(CASE WHEN Type='t3' THEN Value END),0) AS t3,
      COALESCE(SUM(CASE WHEN Type='t4' THEN Value END),0) AS t4
FROM TableName
GROUP BY class

Result:

CLASS   T1  T2  T3  T4
c1      10  3   0   0
c2      4   2   1   0
c3      0   5   0   6

See result in SQL Fiddle.

edited May 20, 2014 at 7:12

answered May 20, 2014 at 7:01

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

4 Comments

Frazz Over a year ago

Why MAX and not SUM? And how is this supposed to handle the dynamic list of types?

Dmitrii Bychenko Over a year ago

Coalesce is more general than IfNull, IsNull, Nvl; Coalesce is supported by almost every DBMS

chrise Over a year ago

unfortunately, t's are not fixed. but pivot seems to be the way to go. was not aware of this feature. thanks

Raging Bull Over a year ago

@chrise: Its always good to learn more. Start from here or here.

Collectives™ on Stack Overflow

sql dynamic table creation with variable columns

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related