postgres query for making required dataset from given table

Question

I have a table which showing different rows and column. I want a dataset from that table. But I have not any idea to make it. So I am showing my table and result from that table... Please help me with making a query to generate that result. Thanks first I am showing my table

UC_Name   |   Word_No   |   entity
 UC-1     -    w-1      -     a
 UC-1     -    w-2      -     a
 UC-1     -    w-3      -     a
 UC-2     -    w-1      -     a
 UC-2     -    w-2      -     a
 UC-2     -    w-2      -     b
 UC-1     -    w-1      -     a
 UC-3     -    w-3      -     b
 UC-4     -    w-2      -     c

Now result as

UC-Name    |    w-1     |     w-2    |    w-3

UC-1       -    2       -     1      -    1
UC-2       -    1       -     2      -    0
UC-3       -    0       -     0      -    1
UC-4       -    0       -     1      -    0

Actually I want entity count in every ward according to UC-Name

(again asking:: I have a table which showing different rows and column. I want a dataset from that table. But I have not any idea to make it. So I am showing my table and result from that table... Please help me with making a query to generate that result. Thanks first I am showing my table)

If the `Word_no` is limited to a predefined set of values then you can use *conditional aggregation* to get what you want. You can find plenty of relevant examples here in SO. — Giorgos Betsos, Sep 02 '17 at 16:52
Yes every UC having 6 words. As UC-1 having word-1,ward-2,ward-3,ward-4,ward-5,ward-6. — user2638158, Sep 02 '17 at 17:06
Possible duplicate of [SQL query to return a grouped result as a single row](https://stackoverflow.com/questions/34244059/sql-query-to-return-a-grouped-result-as-a-single-row) — krokodilko, Sep 02 '17 at 17:39
Please help me ... Make a example query... I am not expert in sql — user2638158, Sep 02 '17 at 19:09

score 1 · Answer 1 · answered Sep 03 '17 at 03:25

From your comments i presume that there will be a fixed number of wards. So since we aren't dealing with a dynamic number of wards, the code below should work for you.

SELECT uc_name
    ,sum(CASE word_no WHEN 'w-1' THEN 1 ELSE 0 END) AS "w-1"
    ,sum(CASE word_no WHEN 'w-2' THEN 1 ELSE 0 END) AS "w-2"
    ,sum(CASE word_no WHEN 'w-3' THEN 1 ELSE 0 END) AS "w-3"
    ,sum(CASE word_no WHEN 'w-4' THEN 1 ELSE 0 END) AS "w-4"
    ,sum(CASE word_no WHEN 'w-5' THEN 1 ELSE 0 END) AS "w-5"
    ,sum(CASE word_no WHEN 'w-6' THEN 1 ELSE 0 END) AS "w-6"
FROM public.table01
GROUP BY uc_name

-HTH

postgres query for making required dataset from given table

1 Answers1