Convert tuple value to column names

Question

Got something like:

+-------+------+-------+
| count |  id  | grade |
+-------+------+-------+
|     1 |    0 | A     |
|     2 |    0 | B     |
|     1 |    1 | F     |
|     3 |    1 | D     |
|     5 |    2 | B     |
|     1 |    2 | C     |

I need:

+-----+---+----+---+---+---+
| id  | A | B  | C | D | F |
+-----+---+----+---+---+---+
|   0 | 1 |  2 | 0 | 0 | 0 |
|   1 | 0 |  0 | 0 | 1 | 1 |
|   2 | 0 |  5 | 1 | 0 | 0 |

I don't know if I can even do this. I can group by id but how would you read the count value for each grade column?

is the first table the result of a query? if so, show your original table structure too as there may be an easier solution to get the final result. — Vamsi Prabhala, Nov 12 '16 at 01:54
Yes it is! I have something that essentially gives id, class, grade. I grouped by ID and grade and counted tuples in that. — Umar Farooq, Nov 12 '16 at 02:48

score 1 · Answer 1 · edited Nov 12 '16 at 11:05

1

CREATE TABLE #MyTable(_count INT,id INT , grade VARCHAR(10))

INSERT INTO #MyTable( _count ,id  , grade )
SELECT 1,0,'A' UNION ALL
SELECT 2,0,'B' UNION ALL
SELECT 1,1,'F' UNION ALL
SELECT 3,1,'D' UNION ALL
SELECT 5,2,'B' UNION ALL
SELECT 1,2,'C' 


SELECT *
FROM
(
 SELECT _count ,id  ,grade
 FROM #MyTable
)A
PIVOT
(
  MAX(_count) FOR grade IN ([A],[B],[C],[D],[F])
)P

edited Nov 12 '16 at 11:05

Abelisto

14,826
2
33
41

answered Nov 12 '16 at 05:28

Mansoor

4,061
1
17
27

1

It is about PostgreSQL, not MS SQL. – Abelisto Nov 12 '16 at 11:07
Ok but not mentioned anywhere.Its helps for MS SQL users – Mansoor Nov 12 '16 at 11:11
1

It's tagged PostgreSQL. Won't get any clearer than this. This answer is not applicable. – Erwin Brandstetter Nov 12 '16 at 14:19

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

You need a "pivot" table or "cross-tabulation". You can use a combination of aggregation and CASE statements, or, more elegantly the crosstab() function provided by the additional module tablefunc. All basics here:

PostgreSQL Crosstab Query

Since not all keys in grade have values, you need the 2-parameter form. Like this:

SELECT * FROM crosstab(
        'SELECT id, grade, count FROM table ORDER BY 1,2'
       , $$SELECT unnest('{A,B,C,D,F}'::text[])$$
       ) ct(id text, "A" int, "B" int, "C" int, "D" int, "F" int);

edited May 23 '17 at 10:29

Community

1
1

answered Nov 12 '16 at 02:15

Erwin Brandstetter

605,456
145
1,078
1,228

Why not simply `VALUES ('A'), ('B'), ('C'), ('D'), ('F')` instead of creating an array and then immediately decomposing it? – Patrick Nov 12 '16 at 04:30
@Patrick: Because it's shorter syntax for more than a few items. – Erwin Brandstetter Nov 12 '16 at 05:04
IMO an article about crosstab/pivot/dynamic pivot should be written in the Documentation section. Unfortunately my English is not so good... – Abelisto Nov 12 '16 at 11:03
@Abelisto: Maybe. I have not warmed up to the whole concept of SO "Documentation", which should really be called something like "Canonical Examples". Postgres has an excellent official documentation that's kept up to date by the project. I am not convinced we should start a parallel Postgres documentation beyond established SO features. But that's really a discussion for meta - and it has been discussed *a lot* there. – Erwin Brandstetter Nov 13 '16 at 06:05
Is there a way without using crosstab? – Umar Farooq Nov 20 '16 at 00:57

Convert tuple value to column names

2 Answers2