asked on

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

chapmandew

Its really, really difficult to dynamically pivot data in SQL Server...you have to write dynamic sql statements to do it.

brejk

See the snippet for some easy example (three dates). If you need some dynamic PIVOT example see one of my older answers: https://www.experts-exchange.com/questions/23051963/subquery-in-pivot-function-sql-server-2005.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

Anthony Perkins

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

rmariotti

ASKER

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

Anthony Perkins

>>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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rmariotti

ASKER

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

Anthony Perkins

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)

Anthony Perkins

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 ' + ...

rmariotti

ASKER

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!

bharat_mane

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)'

Anthony Perkins

>>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?