Please help make this work, I have tried any number of different ways to escape the quotes and not one of them has worked.
It always comes up with a Syntax error, which tells me that I am just not trying it properly and HOPEFULLY someone out there will know the correct syntax -
TRANSFORM Sum(Backlog.Volume) AS SumOfVolume
-
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]
-
FROM Backlog
-
GROUP BY Backlog.SupplyingCompanyID
-
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan '" & YEAR(CURDATE()), ... {through to Dec});
-
Any help that anyone can give will be much appreciated.
The IN clause is optional. If you're trying to limit it to the current year, then you can use the where clause. - WHERE Year([MonthEnding]) = Year(Date())
6 5262
Jet SQL doesn't use the IN clause for PIVOT. It does it automatically.
Thank you for your reply! But I am not sure I understand you, what do you mean when you say it does it automatically? I have seen some examples for using IN clause after PIVOT.
For example: - TRANSFORM Sum(Backlog.Volume) AS SumOfVolume
-
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]
-
FROM Backlog
-
GROUP BY Backlog.SupplyingCompanyID
-
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan '11", "Feb '11", ... {through to "Dec '11"});
Would give me the desired results that I am looking for, however if I do it that way, then the " '11 " is hard coded in and wont roll over in the next year. I am looking to dynamically pull the year part of the CURDATE() function in SQL and have it use that as part of the string "Jan '" & YEAR(CURDATE()) " type of thing, so that next year the I only get '12 volumes.
I have looked for the last couple days for some help with this TRANSFORM statement and can't really find what I am looking for.
Like I said, Jet SQL doesn't use the IN clause. - TRANSFORM Sum(Backlog.Volume) AS SumOfVolume
-
SELECT Backlog.SupplyingCompanyID
-
FROM Backlog
-
GROUP BY Backlog.SupplyingCompanyID
-
PIVOT Format([MonthEnding],"mmm 'yy")
Then could you explain why this code would work? - TRANSFORM Sum(Backlog.Volume) AS SumOfVolume
-
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]
-
FROM Backlog
-
GROUP BY Backlog.SupplyingCompanyID
-
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan '11", "Feb '11","Mar '11","Apr '11","May '11","Jun '11","Jul '11","Aug '11","Sep '11","Oct '11","Nov '11","Dec '11");
If you go to the Microsoft web site they provide the syntax for it as well with an optional IN clause after the PIVOT. http://office.microsoft.com/en-us/ac...001032277.aspx
I am not trying to be ungrateful, I appreciate the fact that you are trying to help me out with this problem, I am just not sure if I am asking my question correctly.
The IN clause is optional. If you're trying to limit it to the current year, then you can use the where clause. - WHERE Year([MonthEnding]) = Year(Date())
HA! that is brilliant! I should have thought about that. That did the trick for sure. Thank you so much for your help! Now I will be posting another SQL question on the main site ...
For those interested final code was: - TRANSFORM Sum(Backlog.Volume) AS SumOfVolume
-
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]
-
FROM Backlog
-
WHERE Year([MonthEnding]) = Year(Date())
-
GROUP BY Backlog.SupplyingCompanyID
-
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan", "Feb","Mar","Apr","May","Jun","Jul","Aug","Sep,"Oct","Nov","Dec");
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John Lehmann |
last post by:
I have an interesting problem. I am performing an XSL
transform using the System.Xml.Xsl.Transform class.
I have a database that contains the XSL style sheet
string. And it seems to work pretty...
|
by: grunar |
last post by:
After some thought on what I need in a Python ORM (multiple primary
keys, complex joins, case statements etc.), and after having built
these libraries for other un-named languages, I decided to...
|
by: Craig Petrie |
last post by:
The following transformation puzzles me when trying to transform XML to XML.
I get an exception "THE EXPRESSION PASSED TO THIS METHOD SHOULD RESULT IN A
NODESET" at the last line...
|
by: Edward S |
last post by:
I would appreciate if someone could correct my SQL statement which is
displaying a message Expected : End of Statement this statment is
attached to a button on the form
StrSQL = "PARAMETERS !!...
|
by: Peter Row |
last post by:
Hi,
I have 2 XML files and 1 XSLT file.
The second XML file has the following declarative 1st line:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
....the 1st one (the one to be...
|
by: Andrew Jocelyn |
last post by:
Hi
Is there a way of outputting some xml tags during an XSLT transform? For
example when I use the 'xsl:value-of select="xhtml"' statement I'd like to
output the children of 'xhtml' after the...
|
by: Steven Bethard |
last post by:
The PEP below should be mostly self explanatory. I'll try to keep the
most updated versions available at:
http://ucsu.colorado.edu/~bethard/py/pep_create_statement.txt...
|
by: jjouett |
last post by:
I have the following input XML:
<?xml version="1.0"?>
<ordersubmit xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"...
|
by: Vijai Kalyan |
last post by:
Hello,
I am trying to use std::transform to take in a collection of strings,
transform them and insert the result into an output container. So, I
wrote something like this:
std::wstring...
|
by: Taras_96 |
last post by:
Hi all,
A poster at http://bytes.com/forum/thread60652.html implies that using
strtoupper in transform doesn't work because ctype.h may define
strtoupper as a macro:
"The problem is that most...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |