How to show all date from a certain Month by horizontally?

Question

I have database table in postgreSQL name as "time" like:

|  Name  |  |    Date1   | |AttendHour1| |    Date2   | |AttendHour2|
---------------------------------------------------------------------
| Zakir1 |  | 2018-10-01 | |   8.00    | | 2018-10-02 | |   8.00    |
| Zakir2 |  | 2018-10-01 | |   9.00    | | 2018-10-02 | |   9.00    |
| Zakir3 |  | 2018-10-01 | |   7.00    | | 2018-10-02 | |   7.00    |

From this table I want the result like..

|  Name  | | 2018-10-01 | | 2018-10-02 |
----------------------------------------
| Zakir1 | |   8.00     | |   8.00     |
| Zakir2 | |   9.00     | |   9.00     |
| Zakir3 | |   7.00     | |   7.00     |

What is postgreSQL Query ?

That's not good even for reporting.. Database work not like spreadsheet.. — dwir182, Oct 31 '18 at 06:37
@dwir182 It's right but i need this query because i want to join two query with matching D1,...,D31 — Zakir Hossain, Oct 31 '18 at 06:43
is that your table design? now october so have 31 days and 31 column days? so 1 table 1 month with 28/29/30/31 column days? — dwir182, Oct 31 '18 at 07:31
hard to do when you have design like that.. consider to restructure your db.. — dwir182, Oct 31 '18 at 07:33

Erwin Brandstetter · Answer 1 · 2018-11-01T01:49:11.537

1

As it stands, you don't even need a crosstab() query for this. Just:

SELECT name, AttendHour1 AS "2018-10-01", AttendHour2 AS "2018-10-02"
FROM   time;

If your desire is to assign column names dynamically from column values: that's not possible. SQL does not allow dynamic column names. You need a two-step workflow:

1. Create the query string dynamically.

To generate above query:

SELECT format('SELECT name, AttendHour1 AS %I, AttendHour2 AS %I FROM time'
             , date1, date2)
FROM   time
LIMIT  1;

2. Execute the query.

edited Nov 01 '18 at 01:49

answered Nov 01 '18 at 01:25

Erwin Brandstetter

539,169
125
977
1,137

here "2018-10-01" and "2018-10-02" both are column value and this value also unknown @Erwin Brandstetter – Zakir Hossain Nov 01 '18 at 01:32
@ZakirHossain: Guessing what you might be getting at, I added some pointers. – Erwin Brandstetter Nov 01 '18 at 14:38

How to show all date from a certain Month by horizontally?

1 Answers1

Linked