For the first case you can use ROW_NUMBER then PIVOT to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.
WITH CTE AS
(
SELECT C,
(ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
(ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row
SQL Fiddle
For the second case Oracle has a straight forward syntax to achieve this with PIVOT. SQL Server doesn't but you can do this as below.
WITH CTE AS
(
SELECT *,
(ROW_NUMBER() OVER (ORDER BY Column1) -1)%5 AS Col,
(ROW_NUMBER() OVER (ORDER BY Column1) -1)/5 AS Row
FROM YourTable
)
SELECT MAX(CASE WHEN Col = 0 THEN Column1 END),
MAX(CASE WHEN Col = 0 THEN Column2 END),
MAX(CASE WHEN Col = 1 THEN Column1 END),
MAX(CASE WHEN Col = 1 THEN Column2 END),
MAX(CASE WHEN Col = 2 THEN Column1 END),
MAX(CASE WHEN Col = 2 THEN Column2 END),
MAX(CASE WHEN Col = 3 THEN Column1 END),
MAX(CASE WHEN Col = 3 THEN Column2 END),
MAX(CASE WHEN Col = 4 THEN Column1 END),
MAX(CASE WHEN Col = 4 THEN Column2 END)
FROM CTE
GROUP BY Row
ORDER BY Row
SQL Fiddle
For the modifications to the question, here is a modified query:
declare @month datetime = '20140708';
set datefirst 1; -- align to Monday as first day of week
-- test data as per OP:
create table #t (
Date Date not null primary key
,IsActive int not null
);
insert #t(Date,IsActive)
values ('20140728', 1)
,('20140729', 0)
,('20140730', 1)
,('20140731', 0)
,('20140801', 1)
,('20140802', 0)
,('20140803', 0);
with -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values
(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)
)E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
select 0 as N union all
select ROW_NUMBER() over (order by N) as N
from E4
)
,calendar as (
select top (datepart(dd, eomonth(@month)))
cast(dateadd(dd,tally.N,@month) as date) as Date
,N
from tally
where N > 0
)
select
WeekNo
,max(isnull(Mon,0)) as Mon
,max(isnull(Tue,0)) as Tue
,max(isnull(Wed,0)) as Wed
,max(isnull(Thu,0)) as Thu
,max(isnull(Fri,0)) as Fri
,max(isnull(Sat,0)) as Sat
,max(isnull(Sun,0)) as Sun
from (
select
calendar.Date
,cast(datename(dw,calendar.Date) as char(3)) as WeekDay
,datepart(ww,calendar.Date) as WeekNo
,data.IsActive
from calendar
left join #t data
on data.Date = calendar.Date
) t
pivot ( max(IsActive) for WeekDay in (Mon,Tue,Wed,Thu,Fri,Sat,Sun)) pvt
group by WeekNo
;
go
yielding this:
WeekNo Mon Tue Wed Thu Fri Sat Sun
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
28 0 0 0 0 0 0 0
29 0 0 0 0 0 0 0
30 0 0 0 0 0 0 0
31 1 0 1 0 1 0 0
32 0 0 0 0 0 0 0
fro the original question:
Not quite sure what this achieves, but the following works nicely:
declare @month datetime = '20140710';
with -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values
(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)
)E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
select 0 as N union all
select ROW_NUMBER() over (order by N) as N
from E4
)
select -- the query requested by OP, using the CTE tally above
[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31]
from (
select top (datepart(dd, eomonth(@month)))
right('0' + cast(N as varchar(2)),2) as X,
N
from tally
where N > 0
) t pivot (max(X) for N in (
[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31] )
) pvt
;
to yield this:
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
(1 row(s) affected)
or with @month int = '20140210' -- February we get:
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 NULL NULL NULL
(1 row(s) affected)
No RDBMS tagged, so used SQL Server syntax above.
Best Answer
fiddle