Solved

can one column hold different data types?

Posted on 2008-10-15
6
146 Views
Last Modified: 2012-05-05
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/
0
Comment
Question by:Camillia
  • 3
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22720631
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 22720648
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
 
LVL 7

Author Comment

by:Camillia
ID: 22720910
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Author Comment

by:Camillia
ID: 22725242
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22725354
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
 
LVL 7

Author Comment

by:Camillia
ID: 22725382
yes, silly me, just looked at BOL. let me try your method. Kamila,
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now