Solved

can one column hold different data types?

Posted on 2008-10-15
6
149 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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