CODEWITHSUNDEEP
sqlsql-server
user11736679
user11736679

Reputation:

(SQL) Split one table's column into two columns based on value

What We have:

Property    Cost
Car 2       0k
Bike        10k
House       500k
Condo       250k
Mobile-Home 90k

Desired:

Property    Cost    Value
Car         11-20k  N/A
Bike         0-10k  N/A
House        N/A    251-500k
Condo        N/A    100-250k
Mobile-home  N/A    21-90k

I have one column for “Property” and one column for “Cost” in the table. I want to make a separate column for “Cost” which I would give an Alias as “Value” and is dependent on what is showing up as the “Property” this would also than hold true for my column of "Cost" as well.

I also need the full ranges to be a possibility for both columns 

I.e 0-10k, 11-20k, 21-100k, 101-250k and 251-500k

How would i write this query? Using MSSS.

Upvotes: 0

Views: 3414

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

This answers the original version of the question.

If I understand correctly, you want case expressions:

select property,
       (case when property in ('car', 'bike') then cost end) as cost,
       (case when property not in ('car', 'bike') then cost end) as value
from t;

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81930

I would perform a simple join on two tables

Not exactly clear how you are toggling between Cost & Value for the final results

Example

Declare @Table1 Table ([Property] varchar(50),[Cost] varchar(50))  Insert Into @Table1 Values 
 ('Car','20k')
,('Bike','10k')
,('House','500k')
,('Condo','250k')
,('Mobile-Home','90k')

Declare @Table2 Table ([Title] varchar(50),[R1] varchar(50),[R2] varchar(50))  Insert Into @Table2 Values 
 ('0-10k',0,10)
,('11-20k',11,20)
,('21-100k',21,100)
,('101-250k',101,250)
,('251-500k',251,500)

Select A.Property
      ,Cost  = IsNull(B.Title ,'N/A')
      ,Value = IsNull(C.Title ,'N/A')
 From @Table1 A
 Cross Apply (values (try_convert(money,replace(Cost,'K',''))) )V(amt)
 Left Join @Table2 B on amt between B.R1 and B.R2 and amt<=20
 Left Join @Table2 C on amt between C.R1 and C.R2 and amt> 20

Returns

Property    Cost    Value
Car         11-20k  N/A
Bike        0-10k   N/A
House       N/A     251-500k
Condo       N/A     101-250k
Mobile-Home N/A     21-100k

Upvotes: 0

CR7SMS
CR7SMS

Reputation: 2584

You could use the lag condition to bring in the value of the next cost:

;with tableA as
(select 'Car' as Property,'20K' as Cost union
select 'Bike','10K' union
select 'House','500K' union
select 'Condo','250K' union
select 'Mobile-Home','90K')

select a.Property,
       case when property in ('car', 'bike') 
                 then ISNULL(LAG(Cost) Over (Order by CAST(Replace(a.Cost,'K','') as int)),'0K') + ' - ' + Cost
            else 'N/A'
            end as cost,
       case when property not in ('car', 'bike') 
                 then ISNULL(LAG(Cost) Over (Order by CAST(Replace(a.Cost,'K','') as int)),'0K') + ' - ' + Cost
            else 'N/A'
            end as value
from tableA a

I have also replaced the NULLs with 'N/A'. Hope this helps.

Upvotes: 0

Robert McKee
Robert McKee

Reputation: 21487

I would create a table "Properties", with columns auto inc int Id, varchar(something) Name, bit IsCost not null.

Then:

SELECT Name,
  (CASE WHEN IsCost=1 THEN COST END) AS cost, 
  (CASE WHEN IsCost=0 THEN COST END) as value
FROM YourTable yt
JOIN Properties p
  ON yt.property = p.Name

If you suspect this might go further (Cost/Value is not intrinsically a boolean), then you may want to either add a IsValue column, or make a column that points to a different table that refers to a propertyType (Cost, Value, Expense, etc etc).

Upvotes: 0

Related Questions