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

605,456
145
1,078
1,228

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