x

PIVOT Issue

Hi,

I have a set of data I need returned with columns being the months from the date ranges being pulled and rows would be the product(recname). Within the month columns would be totals based on the acquired date. As of now the data pulls as:

 Date Acquired      Product        Total New Acquired Items
 December             A                     5
 December             B                     10
 December             C                     7

I would like it to show as

      Jan  Feb  March  ......    Dec 
  A    0    0     0    ......     5
  B    0    0     0    ......     10
  C    0    0     0    ......     7

the above format should be aligned with the month calendars

I have the following Dynamic SQL:

 DECLARE @COLS VARCHAR(MAX)
     SET @COLS = STUFF((SELECT DISTINCT '[' + CONVERT(VARCHAR,BRANDNID) + '],' FROM ITEMS FOR XML PATH('') ),1,1,'')
 
 DECLARE @SQL VARCHAR(MAX)
     SET @SQL = 'SELECT A.[DATE ACQUIRED] 
 FROM (SELECT DATENAME(MONTH, I.ACQUIREDDATE) AS [DATE ACQUIRED] ,  B.RECNAME ,  I.BRANDNID AS [TOTAL NEW ACQUIRED ITEMS]   
             FROM ITEMS I, BRANDS B
             WHERE I.BRANDNID = B.BRANDNID 
             AND CONVERT(DATE,I.ACQUIREDDATE,102) >= ''2010-12-01'' AND CONVERT(DATE, I.ACQUIREDDATE, 102) <= ''2010-12-31''
             GROUP BY I.BRANDNID, B.RECNAME ,DATENAME(MONTH, I.ACQUIREDDATE) ) A
     PIVOT (    
                 COUNT(A.BRANDID) FOR TOTAL IN (' + @COLS + ' )
            ) PVT '
 
 EXEC(@SQL) ;     

The error I am receiving is: Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '42'.

When I run the SELECT statement after STUFF in the dynamic sql above I see that 42 is the first number being pulled from Items but do not see where the syntax error could be.

Can anyone assist me please?

Thanks!

more ▼

asked Jan 04, 2011 at 10:57 AM in Default

avatar image

Noonies
1.2k 62 66 68

can you post the result of select @SQL - it will be easier to find a syntax error in the generated sql rather than having to recreate all your base tables just to run your sql generation script

Jan 04, 2011 at 11:44 AM Kev Riley ♦♦

or failing that the schema and test data for the base tables

Jan 04, 2011 at 11:46 AM Kev Riley ♦♦

I ended up reformatting the query so I'm pivoting on Month since Dynamic SQL is new to me I'm sure it is something I am overlooking so I ended up doing a work around.

Jan 04, 2011 at 01:45 PM Noonies
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you want products in rows and months as columns as you mentioned what you like to show, then you do not need a dynamic query for this.

You can use something like this example. Simply modify it to your table and column names and ad conditions into the "p" query.

 DECLARE @data TABLE (
     Product varchar(10),
     DateAcquired datetime,
     Items int
 )
 
 INSERT INTO @data (Product, DateAcquired, Items)
 SELECT 'A', '2010/12/10', 10 UNION ALL
 SELECT 'A', '2010/12/11', 5 UNION ALL
 SELECT 'A', '2010/11/10', 7 UNION ALL
 SELECT 'A', '2010/08/12', 10 UNION ALL
 SELECT 'B', '2010/12/10', 5 UNION ALL
 SELECT 'B', '2010/12/12', 10 UNION ALL
 SELECT 'B', '2010/10/10', 6 UNION ALL
 SELECT 'C', '2010/12/20', 3 UNION ALL
 SELECT 'C', '2010/12/01', 7 UNION ALL
 SELECT 'A', '2010/11/10', 10 UNION ALL
 SELECT 'A', '2010/07/10', 5
 
 SELECT
     Product,
     ISNULL([1], 0) AS January,
     ISNULL([2], 0) AS February,
     ISNULL([3], 0) AS March,
     ISNULL([4], 0) AS April,
     ISNULL([5], 0) AS May,
     ISNULL([6], 0) AS June,
     ISNULL([7], 0) AS July,
     ISNULL([8], 0) AS August,
     ISNULL([9], 0) AS September,
     ISNULL([10], 0) AS October,
     ISNULL([11], 0) AS November,
     ISNULL([12], 0) AS December
 FROM (
     SELECT
         Product,
         DATEPART(M, DateAcquired) AS AcqMonth,
         Items
     FROM @data
 ) AS p
 PIVOT (
     SUM(Items)
     FOR AcqMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
 ) AS pvt


The result from this query is:

 Product    January     February    March       April       May         June        July        August      September   October     November    December
 ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
 A          0           0           0           0           0           0           5           10          0           0           17          15
 B          0           0           0           0           0           0           0           0           0           6           0           15
 C          0           0           0           0           0           0           0           0           0           0           0           10
more ▼

answered Jan 04, 2011 at 12:05 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Thank you Pavel for your response! I will take a look at what you sent so I can get a "feel" for the code! I'm fairly new and figuring my way around. Thank you all for your help!

Jan 04, 2011 at 01:48 PM Noonies
(comments are locked)
10|1200 characters needed characters left

first pass guess on your code - try adding a space before the '[' in STUFF i.e.

 DECLARE @COLS VARCHAR(MAX)
 SET @COLS = STUFF((SELECT DISTINCT ' [' + CONVERT(VARCHAR,BRANDNID) + '],' FROM ITEMS FOR XML PATH('') ),1,1,'')
more ▼

answered Jan 04, 2011 at 12:02 PM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1089
x114

asked: Jan 04, 2011 at 10:57 AM

Seen: 1411 times

Last Updated: Jan 04, 2011 at 11:48 AM

Copyright 2017 Redgate Software. Privacy Policy