RecipeDan
asked on
SQL Pivot Dates
I have data that looks like this
DayNum DayofWeek Monthname
1 Tuesday April
2 Wednesday April
3 Thursday April
4 Friday April
5 Saturday April
6 Sunday April
7 Monday April
8 Tuesday April
9 Wednesday April
10 Thursday April
11 Friday April
12 Saturday April
13 Sunday April
14 Monday April
15 Tuesday April
16 Wednesday April
17 Thursday April
18 Friday April
19 Saturday April
20 Sunday April
21 Monday April
22 Tuesday April
23 Wednesday April
24 Thursday April
25 Friday April
26 Saturday April
27 Sunday April
28 Monday April
29 Tuesday April
30 Wednesday April
I want to get formatted in rows like this:
Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday
April 1 2 3 4 5 6 7
I tried SQL Pivot but it does not give me the correct results
DayNum DayofWeek Monthname
1 Tuesday April
2 Wednesday April
3 Thursday April
4 Friday April
5 Saturday April
6 Sunday April
7 Monday April
8 Tuesday April
9 Wednesday April
10 Thursday April
11 Friday April
12 Saturday April
13 Sunday April
14 Monday April
15 Tuesday April
16 Wednesday April
17 Thursday April
18 Friday April
19 Saturday April
20 Sunday April
21 Monday April
22 Tuesday April
23 Wednesday April
24 Thursday April
25 Friday April
26 Saturday April
27 Sunday April
28 Monday April
29 Tuesday April
30 Wednesday April
I want to get formatted in rows like this:
Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday
April 1 2 3 4 5 6 7
I tried SQL Pivot but it does not give me the correct results
select *
from
(
select DayNum, DayOfWeek, Monthname
from dim_Date where monthname='April' and yearnum='2014'
) x
pivot
(
Count(DayNum)
for DayofWeek in ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])
) p
Can you explain why you have two Monday columns?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your replies. I will test it and respond back this afternoon.
ASKER
The solutions works great! Thank you