Union all issue - 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)

Union all issue

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-08 : 07:59:33

Hi,

I have the following query:

Select content, label, date as units
from table1
UNION ALL
select content, label, units as units
from table1
select content, label, downloads as units
from table1

Obviously when in union all the data must be in the same format, but I need to have a table like above as the data is being put into a grid in ssrs.

Any ideas?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 08:16:36

You'd have to convert everything to varchar.
SELECT content,label,convert(varchar(10),date,112)
UNION
SELECT content,label,convert(varchar(10),units)
etc.

Jim

Everyday I learn something that somebody else already knew

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-10-08 : 08:17:05

Erm...I can't think how this would ever be useful but you can do this is to use CAST or CONVERT on the UNITs part of the select.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-08 : 08:17:47

The problem with that is, one of the unit values is 3,303,204

it converts it to 3.094-392e which is not what i want.

quote:
Originally posted by jimf

You'd have to convert everything to varchar.
SELECT content,label,convert(varchar(10),date,112)
UNION
SELECT content,label,convert(varchar(10),units)
etc.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 08:36:46

Well, I don't know exactly what you want as you haven't really expressed that. I don't know all the possible values as you didn't post any. If you have a value of 3,303,204 then it is already a varchar and you wouldn't need to convert it. I asumed that units and downloads were int types. If you convert to varchar, you should be okay.

Jim

P.S. How did 3,303,324 get converted to 3.094-392e?

Everyday I learn something that somebody else already knew

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-08 : 08:46:09

It is currently a float, and i converted it to a varchar, and it shows the math sum (dunno what its called for 3mill)

quote:
Originally posted by jimf

Well, I don't know exactly what you want as you haven't really expressed that. I don't know all the possible values as you didn't post any. If you have a value of 3,303,204 then it is already a varchar and you wouldn't need to convert it. I asumed that units and downloads were int types. If you convert to varchar, you should be okay.

Jim

P.S. How did 3,303,324 get converted to 3.094-392e?

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-08 : 08:53:00

Strange days indeed. When I convert it from float I get 3.30332e+006. If you convert it to a numeric first it converts to varchar appropriately. Hopefully one of the gurus here can explain why we get 3.30332e+006.

Jim

Everyday I learn something that somebody else already knew

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-08 : 08:58:43

Thank you!

quote:
Originally posted by jimf

Strange days indeed. When I convert it from float I get 3.30332e+006. If you convert it to a numeric first it converts to varchar appropriately. Hopefully one of the gurus here can explain why we get 3.30332e+006.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources