stevo22 stevo22 - 4 months ago 12
SQL Question

SQL: How to select dates in a range by occurance

The data I have:



USERNAME | DATE
---------------------
USER1 | 7-1-2016
USER1 | 7-5-2016
USER1 | 7-8-2016
USER2 | 7-2-2016
USER2 | 7-5-2016
USER2 | 7-6-2016





I need to select DISCINCT and return something like this:



USER1,7-1-2016,NULL,NULL,NULL,7-5-2016,NULL,NULL,7-8-2016
USER2,NULL,7-2-2016,NULL,NULL,7-5-2016,7-6-2016,NULL,NULL





So, to help understand the need for a query like this.
I'd like to output a list of any given week in a more "calendar like" format, where the data is stored in a more linear (one line per date) format.

Thank You.

Answer

This is called table pivoting. Here's one option using conditional aggregation:

select username,
       max(case when date = '7-1-2016' then date end),
       max(case when date = '7-2-2016' then date end),
       max(case when date = '7-3-2016' then date end),
       max(case when date = '7-4-2016' then date end),
       max(case when date = '7-5-2016' then date end),
       max(case when date = '7-6-2016' then date end),
       max(case when date = '7-7-2016' then date end),
       max(case when date = '7-8-2016' then date end)
from yourtable
group by username
Comments