pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

pivot

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-15 : 11:51:49

This query works if I do not convert to comma separated value.
It gives error when I try to convert the number to comma separated value.
Note that Amount is decimal(15, 2)
Do you see why I get the specified error please?

Thanks

Select
*
from
(
Select
--Amount = Sum(Amount),
Amount = convert(varchar(50), convert(money, Sum(Amount)), 1),
FieldName
From
@tblMain
group by
FieldName
) as summary

PIVOT
(
--Sum(Amount)
--ERROR : Incorrect syntax near the keyword 'convert'.
convert(varchar(50), convert(money, Sum(Amount)), 1)
For [FieldName] in
(
[field1],
[field2],
[field3],
...
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 11:55:08

i didnt understand why you converted aggegated value of amount to varchar in first place.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-15 : 11:56:20

This is the only way I know how to convert from decimal to comma separated value i.e.
154367.23 to 154,365.23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:08:02

quote:
Originally posted by arkiboys

This is the only way I know how to convert from decimal to comma separated value i.e.
154367.23 to 154,365.23

this is a formatting issue and this needs to be dealt with at your front end application rather than at query end. Please dont change the actual datatype for purpose of formatting.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-15 : 12:11:30

Is it not possible to even divide by 1000 in sql using pivot?
i.e. Amount = SUM(Amount)/1000
It does not allow me to do this.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:16:16

quote:
Originally posted by arkiboys

Is it not possible to even divide by 1000 in sql using pivot?
i.e. Amount = SUM(Amount)/1000
It does not allow me to do this.
Thanks

What exactly is your problem. I would appreciate if you could give us a bigger picture. What are you trying to pivot?
Try giving your requirement in below format so as make it easier for somebody to understand your scenario and help you

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-15 : 12:19:54

Ok, please see below where I have placed Error.
Do you see why I get this error please?
Thanks

Select
*
from
(
Select
--Amount = Sum(Amount),
Amount = Sum(Amount)/1000,
FieldName
From
@tblMain
group by
FieldName
) as summary

PIVOT
(
--Sum(Amount)
--ERROR : Incorrect syntax near the keyword '/'.
Sum(Amount)/1000
For [FieldName] in
(
[field1],
[field2],
[field3],
...
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:27:39

quote:
Originally posted by arkiboys

Ok, please see below where I have placed Error.
Do you see why I get this error please?
Thanks

Select
*
from
(
Select
--Amount = Sum(Amount),
Amount = Sum(Amount)/1000,
FieldName
From
@tblMain
group by
FieldName
) as summary

PIVOT
(
--Sum(Amount)
--ERROR : Incorrect syntax near the keyword '/'.
Sum(Amount)/1000
For [FieldName] in
(
[field1],
[field2],
[field3],
...
...
...

why do you need to do Sum(Amount)/1000 inside pivot? isnt Sum(Amount)enough?
and what happened to sample data in format i asked for?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-02-15 : 12:38:39

Ok, I will do the division before the pivot.
I did not realize pivot is so restricted.
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:43:39

quote:
Originally posted by arkiboys

Ok, I will do the division before the pivot.
I did not realize pivot is so restricted.
Thank you

Ok great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources