This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:
DECLARE @x TABLE
(
id INT,
row_num INT,
customer_code VARCHAR(32),
comments VARCHAR(32)
);
INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';
SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments
FROM @x AS x2 WHERE id = x.id
ORDER BY row_num
FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;
If you have a case where the data in comments could contain unsafe-for-XML characters (>
, <
, &
), you should change this:
FOR XML PATH('')), 1, 1, '')
To this more elaborate approach:
FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')
(Be sure to use the right destination data type, varchar
or nvarchar
, and the right length, and prefix all string literals with N
if using nvarchar
.)
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
For the first case you can use
ROW_NUMBER
thenPIVOT
to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.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.SQL Fiddle