January 22, 2008 at 12:40 am
Hi All,
I have a table with following cols. monthwon int,Productcategory varchar(100)
and revenue float.
I want the result in below given format
ProductCategory Jan Feb March
A 2000 5633589
B 1000 64214589
C 3266 15754469
Can I do this in a single query.
Thanks
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 22, 2008 at 1:45 am
Hi Ahmad,
you have not described what is stored in the monthwon column, nor did you give any sample data. So the following will be based on the assumption that monthwon contains the number of the month (if not, you can of course just adjust the query below).
SELECT Productcategory
, [1] AS January
, [2] AS February
, [3] AS March
FROM ( SELECT Productcategory
, revenue
, monthwon
FROM mytable
) p PIVOT ( SUM(revenue) FOR monthwon IN ( [1], [2], [3] ) ) AS pvt
Regards,
Andras
January 22, 2008 at 9:32 pm
Hi,
I am getting an error ... "Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'PIVOT'.
Is pivot is command in sql 2000.
Pls help.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 22, 2008 at 11:15 pm
I have created an temporary table and checked with the following query.
SELECT Pc,
[1] AS January ,
[2] AS February,
[3] AS March FROM
(SELECT Pc, revenue, mon FROM #1) p
PIVOT ( SUM(revenue)
FOR mon IN ( [1], [2], [3] ) ) AS pvt
Checked it is working fine.
January 22, 2008 at 11:22 pm
pivot does not exist in SQL2000
You've posted in the wrong forum.
Dynamic sql is your alternative.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 22, 2008 at 11:53 pm
Hi,
I tried this on sql 2005 express edition...
its not working
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 23, 2008 at 12:05 am
whats the error. SQL 2005 express supports PIVOT
"Keep Trying"
January 23, 2008 at 12:30 am
Hi
It should not give any error. Please paste the query what you are trying to execute.
January 23, 2008 at 12:39 am
Hi,
Fyi.....
SELECT prodmaincat
, [1] AS January
, [2] AS February
, [3] AS March
FROM ( SELECT prodmaincat
, revenue
, oppwonmonth
FROM auditreport
) p PIVOT ( SUM(revenue) FOR oppwonmonth IN ( [1], [2], [3] ) ) AS pvt
pls help
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 23, 2008 at 1:30 am
Check with the following query which is working without any error.
create table #1(pc varchar(1),rev decimal,mon numeric)
insert into #1 values('A',2000,1)
insert into #1 values('B',1000,1)
insert into #1 values('C',3266,1)
insert into #1 values('A',5633,2)
insert into #1 values('B',6421,2)
insert into #1 values('C',1575,2)
insert into #1 values('A',589,3)
insert into #1 values('B',4589,3)
insert into #1 values('C',4469,3)
SELECT pc
, [1] AS January
, [2] AS February
, [3] AS March
FROM ( SELECT pc
, rev
, mon
FROM #1
) p PIVOT ( SUM(rev) FOR mon IN ( [1], [2], [3] ) ) AS pvt
Note:
pc=prodmaincat
mon=oppwonmonth
rev=revenue
January 23, 2008 at 1:49 am
Hi,
It's not working I am getting the same error .
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'PIVOT'.
Thanks,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 23, 2008 at 1:57 am
Ahmad Osama (1/23/2008)
Hi,It's not working I am getting the same error .
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'PIVOT'.
Thanks,
Ahmad
Hi Ahmad,
PIVOT statements have been introduced in SQL Server 2005. (and they work only in SQL Server 2005 or later, and in compatibility mode 90 or higher). Could you tell me what compatibility mode your database is?
You can get this information by executing:
sp_dbcmptlevel 'mydatabasename'
just replace the mydatabasename with the name of your database.
(if it is 80, then you will have to do the pivot in a more complicated way, we can help though).
(Sorry for assuming that you are using SQL Server 2005, for earlier version there is a separate forum :))
Regards,
Andras
January 23, 2008 at 2:27 am
Hi,
compatibility level is 80.
thanks,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
January 23, 2008 at 2:36 am
can you set the dblevel to 90 (SQL2005)
Some functions do not work with dblevel 80.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 2:40 am
Ahmad Osama (1/23/2008)
Hi,compatibility level is 80.
thanks,
Ahmad
Good that explains it 🙂
So first step, can you check the following:
SELECT SERVERPROPERTY('productversion')
Does this start with 9 or 8? If it is 8, then you are running on SQL Server 2000!!! On this PIVOTS are not working (these are a 2005 feature), but you could do something like:
SELECT Productcategory
, SUM(CASE WHEN monthwon = 1 THEN revenue
ELSE 0
END) AS 'January'
, SUM(CASE WHEN monthwon = 2 THEN revenue
ELSE 0
END) AS 'February'
, SUM(CASE WHEN monthwon = 3 THEN revenue
ELSE 0
END) AS 'March'
FROM mytable
GROUP BY Productcategory
If it is 9, you are on SQL Server 2005, so you could, like ALZDBA suggests, just change the compatibility mode with:
sp_dbcmptlevel 'mydatabasename', 90
(change the mydatabasename to your database), following this my original pivot statement should run.
Regards,
Andras
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply