August 4, 2010 at 8:37 pm
Dear All,
May I ask your help on how can I make the date range my column, based from this I will just manually force to have a column [8/1/10], [8/2/10] and so on. I want all distinct dates will be my column with that format or m/d - 8/1, is that possible?
DECLARE @FromDate DateTime;
DECLARE @ToDate DateTime;
SET @FromDate = '8/1/2010'
SET @ToDate = '8/15/2010'
SELECT TeamName,
EmployeeID,
LastName,
MiddleName,
FirstName,
[8/1/10]
FROM (SELECT Teams.TeamName,
Employees.EmployeeID,
Employees.LastName,
Employees.MiddleName,
Employees.FirstName,
ScheduleBreakdown.Date,
Shifts.ShiftName
FROM ScheduleBreakdown INNER JOIN
Employees ON ScheduleBreakdown.IDNumber = Employees.IDNumber INNER JOIN
ScheduleCutOff ON ScheduleBreakdown.CutOffID = ScheduleCutOff.CutOffID INNER JOIN
Shifts ON ScheduleBreakdown.ShiftID = Shifts.ShiftID INNER JOIN
Teams ON ScheduleBreakdown.TeamID = Teams.TeamID AND Employees.TeamID = Teams.TeamID AND
ScheduleCutOff.TeamID = Teams.TeamID
WHERE (ScheduleBreakdown.Date BETWEEN @FromDate AND @ToDate )
) p PIVOT ( MAX([ShiftName])
FOR Date IN ( [8/1/10] ) ) AS pvt
ORDER BY TeamName
I also found a code in the net and try it but im having problem with the syntax of this:
Im using MSSQL 2005
SELECT DISTINCT ColumnText
FROM [#Aggregates] WITH (INDEX(IX_Aggregates), NOLOCK)
ORDER BY ColumnText
SELECT DISTINCT RowText
FROM [#Aggregates] WITH (INDEX (IX_Aggregates), NOLOCK)
CREATE PROCEDURE SchedulePivot
(
@FromDate DATETIME,
@ToDate DATETIME
)
AS
SET NOCOUNT ON
CREATE TABLE #Aggregates
( RowText VARCHAR(50),
ColumnText VARCHAR(50),
ShiftData VARCHAR (50)
)
INSERT INTO #Aggregates
( RowText,
ColumnText,
ShiftData
)
SELECTEmployees.EmployeeID,
ScheduleBreakdown.Date,
MAX(Shifts.ShiftName)
FROM ScheduleBreakdown INNER JOIN
Employees ON ScheduleBreakdown.IDNumber = Employees.IDNumber INNER JOIN
ScheduleCutOff ON ScheduleBreakdown.CutOffID = ScheduleCutOff.CutOffID INNER JOIN
Shifts ON ScheduleBreakdown.ShiftID = Shifts.ShiftID INNER JOIN
Teams ON ScheduleBreakdown.TeamID = Teams.TeamID AND Employees.TeamID = Teams.TeamID AND
ScheduleCutOff.TeamID = Teams.TeamID
WHERE (ScheduleBreakdown.Date BETWEEN '8/1/2010' AND '8/15/2010' )
GROUP BY Employees.EmployeeID,
ScheduleBreakdown.Date
CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, ShiftData)
CREATE TABLE #Columns
( ColumnIndex INT IDENTITY (0, 1),
ColumnText VARCHAR(50)
)
INSERT INTO #Columns
(
ColumnText
)
SELECT DISTINCT ColumnText
FROM [#Aggregates] WITH (INDEX(IX_Aggregates), NOLOCK)
ORDER BY ColumnText
CREATE TABLE #Rows
(
RowText VARCHAR(50)
)
INSERT INTO #Rows
(
RowText
)
SELECT DISTINCT RowText
FROM [#Aggregates] WITH (INDEX (IX_Aggregates), NOLOCK)
DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@SQL VARCHAR(1000)
SELECT @ColumnIndex = 0,
@MaxColumnIndex = MAX(ColumnIndex)
FROM #Columns
WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM #Columns
WHERE ColumnIndex = @ColumnIndex
SELECT @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT NULL DEFAULT 0'
EXEC (@SQL)
SELECT @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.ShiftData
FROM #Aggregates (INDEX(IX_Aggregates), NOLOCK)
, #Columns (INDEX(IX_Columns), NOLOCK)
WHERE #Rows.RowText = #Aggregates.RowText
AND #Columns.ColumnText = #Aggregates.ColumnText
AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
EXEC (@SQL)
SELECT @ColumnIndex = @ColumnIndex + 1
END
DROP TABLE #Columns
DROP TABLE #Aggregates
SELECT #Rows.*
FROM #Rows
ORDER BY #Rows.RowText
DROP TABLE #Rows
August 4, 2010 at 9:00 pm
If I understand you correctly, you want a column for each input date without having to specify the dates in advance, like excel will do. The PIVOT operator doesn't have that ability, BUT you can achieve the same result using dynamic SQL. First you do a select distinct date to get the list of dates and then you dynamically build a PIVOT with a column for each date in the list. Research sp_executeSQL for examples, or maybe search on DYNAMIC PIVOT. If you have any questions after your reading, post them back here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 4, 2010 at 9:25 pm
In addition to what has already been mentioned, you might want to check out the cross-tabs and pivot articles (Parts 1 and 2) - links are in my signature. Part 2 covers dynamic pivots.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 4, 2010 at 10:40 pm
Hi,
Can you please check my code since I'm having syntax problem based on dynamic pivot table I read.
Error is:
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near '8'.
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(8000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(convert(varchar, convert(datetime, ScheduleBreakdown.Date), 1))
FROM ScheduleBreakdown
ORDER BY '],[' + ltrim(convert(varchar, convert(datetime, ScheduleBreakdown.Date), 1))
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query = 'SELECT *
FROM
(
SELECTTeams.TeamName,
Employees.EmployeeID,
Employees.LastName,
Employees.MiddleName,
Employees.FirstName,
ScheduleBreakdown.Date,
Shifts.ShiftName
FROM ScheduleBreakdown INNER JOIN
Employees ON ScheduleBreakdown.IDNumber = Employees.IDNumber INNER JOIN
ScheduleCutOff ON ScheduleBreakdown.CutOffID = ScheduleCutOff.CutOffID INNER JOIN
Shifts ON ScheduleBreakdown.ShiftID = Shifts.ShiftID INNER JOIN
Teams ON ScheduleBreakdown.TeamID = Teams.TeamID AND Employees.TeamID = Teams.TeamID AND
ScheduleCutOff.TeamID = Teams.TeamID
WHERE (ScheduleBreakdown.Date BETWEEN '8/1/2010' AND '8/15/2010' )
) p PIVOT ( MAX([ShiftName])
FOR Date IN ( '+@listCol+' ) ) AS pvt'
EXECUTE (@query)
Based also from that article, I can't make a dynamic pivot table.
August 4, 2010 at 11:28 pm
jessajr (8/4/2010)
Hi,Can you please check my code since I'm having syntax problem based on dynamic pivot table I read.
Error is:
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near '8'.
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(8000)
SELECT @listCol = STUFF(( SELECT DISTINCT
'],[' + ltrim(convert(varchar, convert(datetime, ScheduleBreakdown.Date), 1))
FROM ScheduleBreakdown
ORDER BY '],[' + ltrim(convert(varchar, convert(datetime, ScheduleBreakdown.Date), 1))
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query = 'SELECT *
FROM
(
SELECTTeams.TeamName,
Employees.EmployeeID,
Employees.LastName,
Employees.MiddleName,
Employees.FirstName,
ScheduleBreakdown.Date,
Shifts.ShiftName
FROM ScheduleBreakdown INNER JOIN
Employees ON ScheduleBreakdown.IDNumber = Employees.IDNumber INNER JOIN
ScheduleCutOff ON ScheduleBreakdown.CutOffID = ScheduleCutOff.CutOffID INNER JOIN
Shifts ON ScheduleBreakdown.ShiftID = Shifts.ShiftID INNER JOIN
Teams ON ScheduleBreakdown.TeamID = Teams.TeamID AND Employees.TeamID = Teams.TeamID AND
ScheduleCutOff.TeamID = Teams.TeamID
WHERE (ScheduleBreakdown.Date BETWEEN '8/1/2010' AND '8/15/2010' )
) p PIVOT ( MAX([ShiftName])
FOR Date IN ( '+@listCol+' ) ) AS pvt'
EXECUTE (@query)
Based also from that article, I can't make a dynamic pivot table.
I don't know what's wrong with your code because 1) I don't have your tables so the code won't even come close to running and 2) I don't use Pivot because it's relatively too slow for what I do most of the time.
Now... lemme ask... why do you want to make a pivot table dynamically instead of producing some pivoted (cross tabbed is better) result sets? And, by the way, yes you can make what I think you're asking for as a dynamic pivot table. What makes you think you can't?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2010 at 7:31 am
I think that may be looking at a terminology difference, Jeff. What you can't do (at least according to my reading) with a PIVOT table is just specify a column and say "Turn all values in that column into pivoted columns with headers." PIVOT tables in EXCEL do that, but not SQL Server. You have to expressly list values and give aliases for the column names.
You can dynamically build the PIVOT statement, but the statement itself is fixed when it gets executed.
I'm with you about doing cross-tabs, instead of PIVOT. Cross-tabs are more intuitive to me, but Jess's question was about PIVOT. Speaking of which....
Jess, instead of directly executing the variable containing your SQL, just print it. Then cut-and-paste it into a new window and see what kind of syntax errors you get. That's the fastest way for you to debug it. Listen to Jeff: We have no knowledge of your table or values, so we can't see the final code generated. We're volunteers with day jobs of our own, and while we don't mind helping you out, we really don't want to spend time generating tables and sample data in the dark. If you had provided scripts to create your table(s) and put a little sample data into it, then we could cut and paste, run it, and see the code that is being generated.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 5, 2010 at 7:43 am
FYI, the problem with the code posted is that you have single quotes around dates in your string - as single quotes signify the end of your string, you have to escape these using two single quotes. e.g.:
'
ScheduleBreakdown.Date BETWEEN ''8/1/2010'' AND ''8/15/2010''
'
Which will be converted to single quotes within the string.
August 5, 2010 at 8:23 am
Good eye, Howard.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply