| Author |
Topic |
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
 Posted - 2007-07-30 : 11:24:52
|
| hiI found this link http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tablesI am using SQLExpress and when i try to save the stored procedure it did not allow me to.I used it on SQL Server 2005 and is working fine.Any ideal how can i use this dynamic Cross Tab in SQL Express? Thanks a million. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-30 : 11:40:27
|
| what error do you get?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 11:54:53
|
| Incorrect syntax near the keyword 'Pivot' |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 12:04:43
|
| Also, When i use SQL 2005 using Northwind, is all fine. but the moment i change the query to AdventureWorks, it gave me: Incorrect syntax near the keyword 'Pivot'. It is very strange. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-30 : 12:06:01
|
| Pivot is a reserved word in SQL 2005; try replacing the name used in the article with something else, such as "Pivot2".- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 12:09:23
|
| Ok, it is done. Thanks a million |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-30 : 12:20:56
|
quote:
Originally posted by sg2255551
Also, When i use SQL 2005 using Northwind, is all fine. but the moment i change the query to AdventureWorks, it gave me: Incorrect syntax near the keyword 'Pivot'. It is very strange.
What is your compatibility level set to?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 20:02:38
|
| Yes, now i know the problem. NW was set to 80. Thanks Peso |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 21:11:57
|
| How do I concat two fields together? This is the part: 'Max(DateName(day,StartDate)+'' ''+ Datename(day,EndDate))'execute dbo.crossTab 'SELECT EventTitle,EventGroup FROM dbo.tblEvent INNER JOIN dbo.tblDate ON (tblEvent.EventID = tblDate.EventID)group by EventTitle, EventGroup , 'Max(DateName(day,StartDate)+'' ''+ Datename(day,EndDate))','DATENAME(month, dbo.tblDate.StartDate)','tblDate' |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 21:29:04
|
| Ok, I have manage to solve it. Thanks |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-30 : 22:01:34
|
| how do I do an order by of month in the cross tabFor example: Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec. Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-31 : 02:11:52
|
You mean the order of COLUMNS?Hardcode the PIVOT columns in the query.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sg2255551
Constraint Violating Yak Guru
274 Posts |
Posted - 2007-07-31 : 05:11:32
|
| ok |
|
|
|
|
Dynamic Crosstab in SQLExpress