0

I've been trying to pivot a very sample table:

TYPE       | COUNT
___________________
'CHAINS'   | '38'
'INDEP'    | '64'
'SUPER'    | '25'

I've been reading tutorials, Stack Overflow answers and I've been looking for a good tutorial about transposing a table. I just can't understand it very well and I haven't been able to accomplish the following result:

CHAINS | INDEP | SUPER
_______________________
38     | 64    | 25

I know there are other questions similar to this one, but I just can't get the point from reading. Most examples use multiple tables to explain or just have code with no explanation, and I can't understand what's needed to do this. How can I accomplish this pivoted table?

Edit 1

With the tutorials I've read, I've manage to get the following:

CHAINS | INDEP | SUPER
_______________________
38     | NULL  | NULL
NULL   | 64    | NULL
NULL   | NULL  | 25

I'd like the data to be in a single row without those null. I know there are tutorials and answers about this but I just don't understand clearly. I'd like an explanation of the logic to do this pivot table.

@bluefeet told me I needed an aggregate function. With sum() I got a single row

Edit 2

This is the query I'm using works (thanks to @bluefeet) but it's not dynamic: I can't know for sure what columns I'll need.

SELECT
  sum(case when sq.TYPE = 'CHAINS' then sq.COUNT end) AS CHAINS,
  sum(case when sq.TYPE = 'INDEP' then sq.COUNT end) AS INDEPE,
  sum(case when sq.TYPE = 'SUPER' then sq.COUNT end) AS SUPER
from subquery1 sq

How can I get a similar result but with a dynamic query?

halfer
  • 19,824
  • 17
  • 99
  • 186
Metafaniel
  • 29,318
  • 8
  • 40
  • 67
  • Are there only 3 types or there can be morethan 3? – M Khalid Junaid Sep 24 '14 at 16:23
  • There can be more than those 3 I've listed, that's why a dynamic SQL would be useful. I've just typed those few for the example. Thanks a lot for your kind help – Metafaniel Sep 24 '14 at 16:24
  • @bluefeet Thanks, I'm reading exactly that answer, but I'm not very skilled with SQL, so I still don't get it, I don't understand it clearly u.u I've added to my question the result I'm getting – Metafaniel Sep 24 '14 at 16:33
  • Why do you want to do this? – Strawberry Sep 24 '14 at 16:35
  • @Metafaniel Edit to include your query. It's possible you are grouping by too many columns. If you group by too many columns that are distinct - you might be throwing off the result. – Taryn Sep 24 '14 at 16:35
  • @bluefeet I've added my simple query. It's not even dynamic. – Metafaniel Sep 24 '14 at 16:58
  • @Strawberry Thanks for your interest! Well, I've got a subquery whose result is the first table in this question. I want to add these result to another query for a report and thus need to be in one row. Thanks again – Metafaniel Sep 24 '14 at 17:00
  • @Metafaniel You aren't using an aggregate function -did you try `sum()` around your case like `sum(case when sq.TYPE = 'CHAINS' then sq.COUNT end) AS CHAINS`? – Taryn Sep 24 '14 at 17:19
  • @bluefeet THANKS now it's in one row. BUT this is not dynamic, and I can't know for sure the info that will be the columns =/ So this works but if I hardcode the columns. Thanks again – Metafaniel Sep 24 '14 at 17:26
  • @Metafaniel My answer here -- https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns/12005676#12005676 should help – Taryn Sep 24 '14 at 17:28
  • @Metafaniel If you solved this, then you should post an answer and accept it. – Taryn Nov 07 '14 at 10:39

0 Answers0