pivot tableRSS

2 replies

Last post Aug 04, 2010 02:48 AM by jessajr

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
jessajr
Member

4 Points

110 Posts

pivot table

Aug 03, 2010 04:23 AM|jessajr|LINK

Dear All,

May I ask your help regarding pivot table? I already reads many threads but it doesnt direct me to what I need and If I try to use that Thread but  I'm having problem.

Here's my table:

What I need is the date will be horizontal like this:

Column:     EmployeeID    LastName    FirstName    MiddleName      Date(August 1)    August2     August3

                  EP9112          Gelido           J-R               Bonifacio           ShiftID1               ShiftID2      ShiftID3

Questions:

1. Can I control the date for ex. I want only August 1-15 or August 16-31?

2. Can I concatenate the name with this Gelido, J-R Bonifacio?

Hoping for your knowledge regarding this.
- Reply
ketan_al
Contributor

4790 Points

1211 Posts

Re: pivot table

Aug 03, 2010 11:42 AM|ketan_al|LINK

Hi,

Please refer following

http://www.mssqltips.com/tip.asp?tip=1019

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

hope this helps

Ketan Lohar
http://www.ketscode.com

MCP, MCTS,MCPD (Microsoft Azure Developer)

Please mark as answer if it helps

jessajr

Member

4 Points

110 Posts

Re: pivot table

Aug 04, 2010 02:48 AM|jessajr|LINK

Thanks for the link. But I still need help based on how I can make a pivot if I have unknown column.

Based from this query, I need to set my column from the fromdate and todate, I just set data to the variable for testing. So instead of setting [8/1/2010] manually, I need to set it from FromDate and increment a day until ToDate is met. Hoping for your idea regarding this. I'm using MSSQL 2005 Thank you very much.

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 fount a code but I experiencing error:

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
            )

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

CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )
 
INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates (INDEX(IX_Aggregates), NOLOCK)
ORDER BY       ColumnText
 
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE #Rows 
             (
              RowText VARCHAR(50)
             )
 
INSERT INTO    #Rows
                (
                 RowText
                )

SELECT DISTINCT RowText
FROM            #Aggregates (INDEX(IX_Aggregates), NOLOCK)
 
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
 
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.CellData
                         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

Error is:

Msg 121, Level 15, State 1, Procedure SchedulePivot, Line 17

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Msg 1018, Level 15, State 1, Procedure SchedulePivot, Line 60

Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Msg 1018, Level 15, State 1, Procedure SchedulePivot, Line 76

Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

‹ Previous Thread|Next Thread ›