Pivoting Data

I have a dataset that I need to figure out how to pivot on.  SELECT statement is below.  This results in the following result set:

Date       Type        ItemCode
3/3         A                  BNT
3/3         B                  ORG
3/3         C                  ABC
3/4         A                  JPG
3/4         B                  UIN
3/4         C                  JRG
3/5         A                 YRK
3/5         B                  KBC
3/5         C                  RDK
......................

Type       3/3     3/4      3/5
A           BNT    JPG    YRK
B           ORG    UIN     KBC
C           ABC    YRK   RDK
...

I need a solution that takes in a dynamic date range, and does not require me to define the data as a column.  Thanks!
SELECT mm.Date, mm.ItemType, mi.ItemCode
FROM MenuMaster mm
INNER JOIN MenuItem mi ON mm.MenuItemID = mi.MenuItemID
WHERE Date BETWEEN '3/1/2008' AND '3/31/2008'

Open in new window

rmariottiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Its really, really difficult to dynamically pivot data in SQL Server...you have to write dynamic sql statements to do it.
0
brejkCommented:
See the snippet for some easy example (three dates). If you need some dynamic PIVOT example see one of my older answers: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23051963.html.
SELECT Q.ItemType, [3/3/2008], [3/4/2008], [3/5/2008] 
FROM
(
  SELECT mm.Date, mm.ItemType, mi.ItemCode
  FROM MenuMaster mm
  INNER JOIN MenuItem mi ON mm.MenuItemID = mi.MenuItemID
  WHERE Date BETWEEN '3/1/2008' AND '3/31/2008'
) AS Q
PIVOT (
  MAX(Q.ItemCode)
  FOR mi.Date IN ([3/3/2008],[3/4/2008],[3/5/2008])
) AS P
ORDER BY Q.ItemType

Open in new window

0
Anthony PerkinsCommented:
See here:

Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

Cross-tab Queries with Transact-SQL
http://www.itrain.de/knowhow/sql/tsql/pivot/pivotsample.asp
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rmariottiAuthor Commented:
Executing the cross-tab "sp_transform" yield the following error.  Here's my select.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

DECLARE      @return_value int

EXEC      @return_value = [dbo].[transform]
            @Aggregate_Function = N'MAX',
            @Aggregate_Column = N'MenuItemID',
            @TableOrView_Name = N'MenuMaster',
            @Select_Column = N'ItemType',
            @Pivot_Column = N'Date'

SELECT      'Return Value' = @return_value
0
Anthony PerkinsCommented:
>>Incorrect syntax near the keyword 'with'.<<
My mistake.  I thought you were using SQL Server 2000.  Since you are using SQL Server 2005 you may be better off checking out the PIVOT clause.
0
Anthony PerkinsCommented:
Here is a good article on the subject:

Pivot table for Microsoft SQL Server
http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rmariottiAuthor Commented:
acperkins:  We're close.  I tried the pivot table example from the article above.  The table seems to render properly with NULL in the Cell Data and the following error returned repeatedly.

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Ideas?
ALTER PROCEDURE sp_Calendar_GetMenuItemCodesByDate
(
   @StartDate DATETIME,
   @EndDate DATETIME
)
 
AS
 
SET NOCOUNT ON
 
CREATE TABLE #Aggregates
             (
              RowText VARCHAR(100),
              ColumnText VARCHAR(100),
              CellData VARCHAR(100)
             )
 
INSERT INTO #Aggregates
            (
             RowText,
             ColumnText,
             CellData
            )
SELECT      mm.ItemType,
            CONVERT(VARCHAR(15), mm.Date, 101),
            mi.ItemCode
FROM        MenuMaster mm
INNER JOIN  MenuItem mi ON mm.MenuItemID = mi.MenuItemID
WHERE       mm.Date BETWEEN @StartDate AND @EndDate
 
CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)
 
CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(100)
             )
 
INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates WITH(INDEX(IX_Aggregates), NOLOCK)
ORDER BY       ColumnText
 
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE #Rows 
             (
              RowText VARCHAR(100)
             )
 
INSERT INTO    #Rows
                (
                 RowText
                )
 
SELECT DISTINCT RowText
FROM            #Aggregates WITH(INDEX(IX_Aggregates), NOLOCK)
 
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
 
DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(100),
            @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) + ' VARCHAR NULL'
      EXEC       (@SQL)
 
      SELECT     @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
                         FROM #Aggregates WITH(INDEX(IX_Aggregates), NOLOCK)
								    , #Columns WITH(INDEX(IX_Columns), NOLOCK) 
								 WHERE #Rows.RowText = #Aggregates.RowText 
								 AND #Columns.ColumnText = #Aggregates.ColumnText 
								 AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(100))
      EXEC       (@SQL)
 
      SELECT  @ColumnIndex = @ColumnIndex + 1 
   END
 
DROP TABLE #Columns
DROP TABLE #Aggregates
 
SELECT     #Rows.*
FROM        #Rows
ORDER BY   #Rows.RowText
 
DROP TABLE  #Rows

Open in new window

0
Anthony PerkinsCommented:
That should be pretty easy to troubleshoot.  Since it happened repeatedly it should be in the loop and in particular the line:
EXEC       (@SQL)

If you focus on the part you changed namely:
SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR NULL'
From:
SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT DEFAULT 0'

You will notice that you chose to use VARCHAR and since you did not define the length of the VARCHAR it defaulted to 1.  So the fix is that if you want to use VARCHAR you need to define it larger enough for example VARCHAR(50) or VARCHAR(100)
0
Anthony PerkinsCommented:
Byt this:
>>
Since it happened repeatedly it should be in the loop and in particular the line:
EXEC       (@SQL)
<<
I meant the EXEC (@SQL) after:
SELECT     @SQL = 'UPDATE #Rows SET ' + ...
0
rmariottiAuthor Commented:
Wow...can't believe I missed that.  I knew it was in that statement, but just didn't see it.  Thanks for helping me through it!
0
bharat_maneCommented:

In the above qry of  rmariotti:

if change is made in this line from remove null and specify length of varchar then it will not through
"String or binary data would be truncated." error
 SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR NULL'
change like
 SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(100)'
0
Anthony PerkinsCommented:
>>if change is made in this line from remove null and specify length of varchar then it will not through<<
Two quetions for you:
1. Did you not see that I already stated that? Here is a refresher:
You will notice that you chose to use VARCHAR and since you did not define the length of the VARCHAR it defaulted to 1. So the fix is that if you want to use VARCHAR you need to define it larger enough for example VARCHAR(50) or VARCHAR(100)

2. What in the world are you trolling old clsoed questions from over 6 months ago?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.