0

This is the table:

select * from USER_LOGIN;

| DATE_LOGIN |ID_USER|
+============+=======+
| 2021/12/01 |  0001 | 
| 2021/12/01 |  0032 | 
| 2021/12/04 |  0002 | 
| 2021/12/09 |  0005 | 
| 2021/12/10 |  0001 | 
| 2021/12/10 |  0005 | 
| 2021/12/11 |  0032 | 
| 2021/12/11 |  0016 | 
| 2021/12/11 |  0001 | 
| 2021/12/12 |  0002 | 
........

The Column "DATE_LOGIN" is not a fixed date, it may change randomly in the future. I want to run select query to have value of DATE_LOGIN as column and values is count ID_USER

Output required:

| null | 2021/12/01 | 2021/12/04 | 2021/12/09 | 2021/12/10 | 2021/12/11 | 2021/12/12 |.....
+======+============+============+============+============+============+============+
| null |     2      |     1      |     1      |     2      |     3      |     1      |.....

Query I tried:

SELECT DATE_LOGIN, COUNT(ID_USER) COUNT_ID_USER
FROM USER_LOGIN
GROUP BY DATE_LOGIN

but output is not as expected

| DATE_LOGIN |ID_USER|
+============+=======+
| 2021/12/01 |   2   | 
| 2021/12/04 |   1   | 
| 2021/12/09 |   1   | 
| 2021/12/10 |   2   | 
| 2021/12/11 |   3   | 
| 2021/12/12 |   1   |

Please help me change row to column and have 1 column in the first. Thank you so much.

Trường Giang Nguyễn
  • 19
  • 3
  • 1
    This is **not a task suitable for SQL** use your app's presentation manager. What you are attempting is 2 fold. First, you are `Pivoting` columns to rows. This can be but you need to know the values before submitting the query. Secondly you are trying to use data values as alias/column headers. To do this you meed to know exactly the values and the exact order in advance then you need to list them individually. Use SQL to retrieve dates and counts but the app to generate the output layout, – Belayer Dec 18 '21 at 16:02
  • 1
    Why do you (or "why do your business users") want something like this in the first place? It is definitely not suitable for any further processing (by a computer), so it must be for reporting purposes - for a human reading a report. But who is going to read a report like that? Imagine you have data for a full year (or even for a month) - who can make sense of data in 365 columns, or even in 30 columns? This is **why** there isn't much support for such an operation. You can still do it, but not easily; reporting systems can do it if they support it. –  Dec 18 '21 at 16:11
  • The dynamic methods [here](https://stackoverflow.com/a/65055157/5841306) for pivoting might help if you're using Oracle SQL Developer – Barbaros Özhan Dec 19 '21 at 08:34

0 Answers0