can one column hold different data types?

i think my answer is no. this is the situtation:
i have one column that now is "money". But i have data that i want to hold in this column that's percentage as well. right now, when i display this column thru my code...i see the percentage as $:

 select total from tableand when i display it in code..i see:

My amount: $6000
My percentage: $8.1  (this should be 8.1%)

--- i thought about having it as varchar, then casting it. i thought about having a case statement...
case when  "money" then <cast value as money>
       when "percentage" then <cast value as percentage>

but not sure..any ideas/
LVL 7
CamilliaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
momi_sabagConnect With a Mentor Commented:
there is also a datatype called sql_variant which can holds different data types in different rows and when you select that column you have to cast it but i don't know if you want to use that data type
it is rather complex and error prone
0
 
momi_sabagCommented:
the casting would work,
the question is, who puts the $ sign there
that is probably done by the client you use and not by the sql server engine
0
 
CamilliaAuthor Commented:
i think the client puts $. Not sure tho..wouldnt "money" field put $ automatically? have to look into it...

but still with sql_variant, we have to "cast". it's ok tho. I'll look into it.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
CamilliaAuthor Commented:
what's wrong with this case when i try to covert?? get an error with percent

select
   case coltype when 'A' then convert(money,colvalue)
                when 'P' then convert(percent,colvalue)
                else colvalue
  end
from testtable
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
percent is not a data type.

select
   case coltype when 'A' then cast(convert(money,colvalue) as varchar(20))
                when 'P' then cast(convert(decimal(20,2),colvalue) as varchar(20)) + '%'
                else colvalue
  end
from testtable

Open in new window

0
 
CamilliaAuthor Commented:
yes, silly me, just looked at BOL. let me try your method. Kamila,
0
All Courses

From novice to tech pro — start learning today.