CODEWITHSUNDEEP
sqlpostgresql-9.1
Zakir Hossain
Zakir Hossain

Reputation: 444

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

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 ?

Upvotes: 0

Views: 359

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657202

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.

Upvotes: 1

Related Questions