Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PIVOT SQL 2008

Posted on 2009-07-15
16
Medium Priority
?
2,521 Views
Last Modified: 2012-06-27
v2008, I've used this transform a million times successfully for cross-tab/pivot resultsets:
http://www.itrain.de/knowhow/sql/tsql/pivot/pivotsample.asp

it isn't working now, and i just am seriously tired of trying to fix it.   my failure is this:
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, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


I've read and read many references out there as to the cause of this, but it's not a CTE per se and again, i'm just seriously tired of working this one, i would like to use the new PIVOT in v2008.

every reference I've seen on PIVOT, however, suggests that I have to hard-code an IN list.  surely I am misinterpretting this.   for example:

USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
( COUNT (PurchaseOrderID)
FOR EmployeeID IN  ( [164], [198], [223], [231], [233] )   <<<<<<<<<< THIS IN LIST
) AS pvt
ORDER BY pvt.VendorID;


i just want that code below to return this:

             symbol1, symbol2, symbol3...........
date1          12      157           15
date2         100         6000       3599
date3         538        752          7
.........
....

Do I have to do the PIVOT with dynamic sql?  
SELECT Symbol,
CONVERT(VARCHAR(10),DateField,101) AS [Date],
SUM(quantity) AS Volume
FROM tablename
WHERE field='XYZ' 
AND (symbol LIKE 'xx%' OR symbol LIKE 'yy%' OR symbol LIKE 'zz%' OR symbol LIKE 'JJ%' or symbol IN ('aa','bb','cc')
OR Symbol LIKE 'klm__')
GROUP BY Symbol,CONVERT(VARCHAR(10),DateField,101)

Open in new window

0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 24863382
No the values must be hard coded.  This can be worked around typically by generating dynamic SQL that gathers the values for you and dynamically constructs the ultimately executed PIVOT query that has hard coded values in the IN statement.

Mark Wills has written a great article on this along with a working stored procedure that will handle all this for you.

http:/viewArticle.jsp?aid=653

I would only ask that if it does indeed help you, that you appropriately vote yes to its helpfulness at the top of the article.  

Hope that helps.

Best regards,
Kevin
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24863447
I will check it out this evening, Kevin.  Thank you much.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24863743
wow.    not there yet, but very first go is pretty darned good.   back soon
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 17

Author Comment

by:dbaSQL
ID: 24863754
just want to play with the NULLs that you get if/when a value is not there on a given day.  back soon...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24864157
If it is a finite list, you can always explicitly list out the column list and then just wrap the column with COALESCE or ISNULL, but see how your tests go.  Definitely how it functions though if the column that ends up as your row identifier doesn't match to all the pivoted columns.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24868582
so.  i create a test table.  tstVOLUME  -- ID, Symbol, Date, Volume
within which i insert that code up there:

SELECT Symbol,
CONVERT(VARCHAR(10),DateField,101) AS [Date],
SUM(quantity) AS Volume
FROM tablename
WHERE field='XYZ'
AND (symbol LIKE 'xx%' OR symbol LIKE 'yy%' OR symbol LIKE 'zz%' OR symbol LIKE 'JJ%' or symbol IN ('aa','bb','cc')
OR Symbol LIKE 'klm__')
GROUP BY Symbol,CONVERT(VARCHAR(10),DateField,101)

the source table is 6 months of data, 85,433,421 records.  as you can see, I use no time parms.  
the insertion itself takes exactly :01 second.  It writes 2333 rows.  
i then run the code snippet below.  it, too, completes instantly.  (less than a second), and returns exactly the data i am looking for.

i need to wrap that code snippet into a procedure.  no biggie.
and I want to return '0' instead of 'NULL' if/when a symbol wasn't traded on a particular day.
i also need to dump it to csv, so i may do something in SSIS, but but other than that, this is pretty damn good.  lightening fast.  excellent.

Kevin, very big thank you.
Mark, thanks to you as well.  Very clever solution.  
DECLARE 
@sourcedata varchar(8000),
@Pivot_On_Source_Column varchar(2000), 
@Pivot_Value_Aggregate varchar(10), 
@Pivot_Value_Column varchar(2000), 
@Pivot_Column_List varchar(2000),
@Pivot_Column_Style_Code varchar(4)  -- used in convert for style code
 
set @sourcedata = 'tstVOLUME'
set @pivot_on_source_column = 'Date'
set @pivot_value_aggregate = 'sum'
set @pivot_value_column = 'Volume'
set @pivot_column_list = 'Symbol'
set @pivot_column_style_code = ',106'
 
-- we really should put in some error checking, e.g. if anything is NULL it will crash.
   declare @columns varchar(max)
   declare @sql nvarchar(max)
 
   set @sql = N'set @columns = substring((select '', [''+convert(varchar,'+@Pivot_Column_List+@Pivot_Column_Style_Code+')+'']'' from '+@sourcedata+' group by '+@Pivot_Column_List+' for xml path('''')),2,8000)'
   execute sp_executesql @sql,
                         N'@columns varchar(max) output',
                         @columns=@columns output 
 
   set @sql = N'SELECT * FROM 
       (SELECT '+@Pivot_On_Source_Column+','+@Pivot_Column_List+','+@Pivot_Value_Column+' from '+@sourcedata+') src
       PIVOT
       ('+@Pivot_Value_Aggregate+'('+@Pivot_Value_Column+') FOR '+@Pivot_Column_List+' IN ('+@columns+') ) pvt
       ORDER BY '+@pivot_on_source_column+''
   execute sp_executesql @sql

Open in new window

0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 31603942
Excellent.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24868785
You are most welcome, dbaSQL.

Happy coding!

Best regards,
Kevin

P.S. since you did find it helpful, please don't forget to vote on Mark's article. ;)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24868796
Yep.  Already have.   Thanks again, Kevin.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24869726
just a quicky...  i'm trying to ISNULL my @pivot_column_list

original:
  set @sql = N'set @columns = substring((select '', [''+convert(varchar,'+@Pivot_Column_List+@Pivot_Column_Style_Code+')+'']'' from '+@sourcedata+' group by '+@Pivot_Column_List+' for xml path('''')),2,8000)'

attempt:
  set @sql = N'set @columns = substring((select '', [''+convert(varchar,(ISNULL('+@Pivot_Column_List+',0)'+@Pivot_Column_Style_Code+')+'']'' from '+@sourcedata+' group by '+@Pivot_Column_List+' for xml path('''')),2,8000)'


output:
Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24870491
you have an extra ( in your line right before the ISNULL:

  set @sql = N'set @columns = substring((select '', [''+convert(varchar,ISNULL('+@Pivot_Column_List+',0)'+@Pivot_Column_Style_Code+')+'']'' from '+@sourcedata+' group by '+@Pivot_Column_List+' for xml path('''')),2,8000)'
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24870609
Don't think that is the correct placement for that adjustment.  You would need the ISNULL within the select list which you see is "SELECT * FROM ...".  You would have to specify the hard coded value (e.g. Symbol2) there like this "SELECT ISNULL(Symbol2, 0) FROM ...".
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24871852
that's not going to work.  (don't want to hard code) they symbols are dynamic
it's fine, though
thank you again, kevin
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24872088
last one, i promise.
can this be done on v2000?  i've put a proc together in v2008, it all works perfectly.
but, i have to run it on my historical data.... which is still sitting in v2000

max and for xml path maybe not.  curious if you had any ideas
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24872146
PIVOT keyword is not available in SQL Server 2000, sorry.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24872172
One thought would be to create a linked server to your SQL Server 2000 instance and then on your SQL Server 2005 instance try to create a view from data using four part naming:

CREATE VIEW vw_HistoricalData
AS
SELECT * FROM linkedserver.database.dbo.tablename;

Then try to use this view in your stored procedure to PIVOT.

Not sure that will work, but in theory it should.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question