November 21, 2013 at 4:57 pm
I have table :
IDDate Location
12013-11-01 H1
22013-11-02 H1
32013-11-01 H2
42013-11-02 H2
and i need to create view calendar like the attached but the view will have 31 days even it is no record
November 21, 2013 at 5:54 pm
Even though stuff like that should be done at the presentation layer, it could also be achieved using T-SQL.
The "magic word" is CrossTab (see the related link in my signature).
Since the column names are static, you won't need the dynamic CrossTab approach.
If you'd like to see a coded solution please provide ready to use sample data (as described in the first link in my signature).
November 21, 2013 at 6:13 pm
Besides of the CROSS TABS, you should review the concept of a Tally Table and I believe you might want to check on creating character-separated lists to have just one row per day.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
November 21, 2013 at 6:25 pm
As it might get complicated for someone who is not related to all of the techniques mentioned, here's an example using a CTE for the sample data.
Check it and ask any questions you have. Be sure to understand the code before implementing it, because you'll be in charge of it.
WITH SampleData(ID,Date, Location) AS(
SELECT 1,'20131101', 'H1' UNION ALL
SELECT 2,'20131102', 'H1' UNION ALL
SELECT 3,'20131101', 'H2' UNION ALL
SELECT 4,'20131102', 'H2'
),
Data AS(
SELECT DAY(Date) AS DayNo,
MONTH(Date) AS MonthNo,
STUFF( (SELECT CHAR(13) + 'ID:' + CAST( ID AS varchar(15)) + ' Location:' + Location
FROM SampleData x
WHERE x.Date = s.Date
FOR XML PATH(''),TYPE).value('.','varchar(max)'), 1,1,'') AS Data
FROM SampleData s
GROUP BY Date
)
SELECT x.DayNo,
MAX(CASE WHEN MonthNo = 1 THEN Data ELSE '' END) Jan,
MAX(CASE WHEN MonthNo = 2 THEN Data ELSE '' END) Feb,
MAX(CASE WHEN MonthNo = 3 THEN Data ELSE '' END) Mar,
MAX(CASE WHEN MonthNo = 4 THEN Data ELSE '' END) Apr,
MAX(CASE WHEN MonthNo = 5 THEN Data ELSE '' END) May,
MAX(CASE WHEN MonthNo = 6 THEN Data ELSE '' END) Jun,
MAX(CASE WHEN MonthNo = 7 THEN Data ELSE '' END) Jul,
MAX(CASE WHEN MonthNo = 8 THEN Data ELSE '' END) Aug,
MAX(CASE WHEN MonthNo = 9 THEN Data ELSE '' END) Sep,
MAX(CASE WHEN MonthNo = 10 THEN Data ELSE '' END) Oct,
MAX(CASE WHEN MonthNo = 11 THEN Data ELSE '' END) Nov,
MAX(CASE WHEN MonthNo = 12 THEN Data ELSE '' END) Dec
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31))x(DayNo)
LEFT
JOIN Data d ON x.DayNo = d.DayNo
GROUP BY x.DayNo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply