February 27, 2011 at 2:07 am
Hi There,
I am very new to Pivot tables and have seen a few examples in practice however none like my current need...
I have the following tables:
Classes
>ClassID
>CL_Description
>CL_StartDate
>CL_NumberOfClassesForTerm
Members
>MemberID
>ME_Name
>ME_Surname
>ME_Class_Link
I have omitted the rest for brevity.
I need to create a SQL Query that lists all the members for a specified ME_Class_Link down on the left (Which is of course the easiest part)
But I need to display the Weekly dates as the column headers across in weekly increments starting from CL_StartDate for the CL_NumberOfClasses
I.E.
The headers of the table should be:
Member Name,1 Feb,8 Feb,15 Feb,22 Feb, (For the number of Classes specified in CL_NumberOfClasses)
The actual Values need to be empty for now (It is merely a printable class register to tick off who was present for the classes on those weekly dates)
I don't even know where to start to generate the weekly dates to put into the pivot...
Really hope someone can help... 🙂
February 27, 2011 at 2:13 am
If the dates are fixed, you can use one of the normal pivot techniques here:
http://www.sqlservercentral.com/articles/T-SQL/63681/
If the dates are not fixed, and the columns need to be generated dynamically, see:
http://www.sqlservercentral.com/articles/65048/
Alternatively, try something like this (demo code):
SET NOCOUNT ON;
SET STATISTICS XML OFF
;
CREATE TABLE
#Sample
(
company VARCHAR(10) NOT NULL,
period INTEGER NOT NULL,
sales_value MONEY NOT NULL
)
;
INSERT #Sample (company, period, sales_value)
VALUES ('A', 200901, $25000),
('A', 200902, $12000),
('A', 200902, $22000),
('A', 200903, $18000),
('A', 200904, $19000),
('A', 200904, $11000),
('A', 200904, $12000),
('A', 200905, $23000),
('A', 200906, $32000),
('B', 200901, $11000),
('B', 200902, $15000),
('B', 200903, $19000),
('B', 200903, $11000),
('B', 200904, $12000),
('B', 200905, $21000),
('B', 200905, $17000),
('B', 200905, $13000),
('B', 200906, $14000)
;
DECLARE Periods
CURSOR LOCAL
FORWARD_ONLY
FAST_FORWARD
READ_ONLY
FOR SELECT DISTINCT S.period
FROM #Sample AS S
;
DECLARE @period INTEGER,
@pstring CHAR(6),
@sql VARCHAR(MAX)
;
OPEN periods
;
FETCH NEXT
FROM Periods
INTO @period
;
SET @sql = SPACE(0);
WHILE @@FETCH_STATUS = 0
BEGIN
SET @pstring = CONVERT(CHAR(6), @period);
SET @sql +=
', SUM(CASE WHEN period = ' + @pstring +
' THEN sales_value END) AS ' + QUOTENAME(@period)
;
FETCH NEXT
FROM Periods
INTO @period
;
END
;
CLOSE Periods; DEALLOCATE Periods
;
SET @sql =
'SELECT company ' + @sql +
'FROM #Sample ' +
'GROUP BY company'
;
EXECUTE (@sql)
;
DROP TABLE
#Sample
;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 2:34 am
Hi Sqlkiwi,
Thanks for the reply.
Most of these Pivot techniques I am aware of, although in this specific instance I do not have an actual data table which has periods and sales values to work with.
It is simply a list of members and a column list of weekly dates. Nothing to aggregate or select from - this is why I'm baffled of how to begin.
Here is an example of the End Result I need:
Member Name | 1 Feb | 8 Feb | 15 Feb | 22 Feb ... (For a set number of weeks (No End date supplied))
Samantha
Paul
John
Gerhard
Harry
Micheal
Thats it - No values, no aggregates, no totals.
The pivot seems to depend on having actual data in the members table to draw from for a specific period, which I don't.
I think my main problem is figuring out how to generate the weekly interval dates to begin with...
February 27, 2011 at 3:01 am
riaan-777462 (2/27/2011)
I think my main problem is figuring out how to generate the weekly interval dates to begin with...
Perhaps this gives you an idea:
DECLARE @Classes
TABLE (
ID INT PRIMARY KEY,
Start DATETIME NOT NULL,
Number INTEGER NOT NULL
)
;
INSERT @Classes
(
ID,
Start,
Number
)
VALUES (1, '23 Feb 2011', 13),
(2, '01 Apr 2011', 26)
;
WITH Numbers (n)
AS (
-- Generates numbers 1 - 520
-- Could use a permanent numbers table instead
SELECT TOP (520)
ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.all_columns AC1,
master.sys.all_columns AC2,
master.sys.all_columns AC3
)
SELECT C.ID,
ClassDate = DATEADD(WEEK, N.n, C.Start)
FROM @Classes AS C
JOIN Numbers AS N
ON N >= 1
AND N <= C.Number
ORDER BY
C.ID,
ClassDate
;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 5:22 am
Hi Paul,
Thank you! Thank you! Thank you!
The second part works brilliantly if I replace it with the actual start date and numofclasses column from my existing Classes table! 🙂
An excellent start - I still have no idea what half of it means but I'll figure it out from here - The pivot should also not be too much of a problem now either -Thanks again! 🙂
xRiaan
February 27, 2011 at 5:35 am
You're welcome. You should probably create a permanent numbers table - they come in very handy for all sorts of things.
See http://www.sqlservercentral.com/articles/T-SQL/62867/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2011 at 5:39 am
Thanks Paul, I just did so - Simplifies the query a lot. 🙂
February 27, 2011 at 6:26 am
I came completely right! With big thanks to Paul and another tutorial at http://www.kodyaz.com 🙂
Below is the final T-SQL query for anyone who has a similar issue: (I will obviously allow the hardcoded Class and Term links to be collected by the StoredProc's params)
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ', ','') + '['+ Convert(varchar(50),DATEADD(WEEK, N.n, CH_Start_Date),106) + ']'
FROM ClassHistory
JOIN Numbers AS N
ON N >= 1
AND N <= CH_NumOfClasses
Where CH_Class_Link=1 and CH_Term_Link=1
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'SELECT *
FROM (
SELECT TOP 520 '''' as val, ME_Name, Convert(varchar(50),DATEADD(WEEK, N.n, CH_Start_Date),106) as ClassDate
FROM ClassHistory
INNER JOIN MemberHistory ON MH_Class_Link=CH_Class_Link
INNER JOIN Members ON MemberID=MH_Member_Link
JOIN Numbers AS N
ON N >= 1
AND N <= CH_NumOfClasses
Where CH_Class_Link=1 and CH_Term_Link=1
ORDER BY ClassDate
) AS PivotData
PIVOT (min(val) FOR ClassDate IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable'
EXECUTE(@PivotTableSQL)
Hope it helps!
xR
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply