?
Solved

can one column hold different data types?

Posted on 2008-10-15
6
Medium Priority
?
165 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 1000 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
The 14th Annual Expert Award Winners

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

 
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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