CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bruce
Bruce

Reputation: 99

SQL Server 2008 join multiple columns to form one column

I'm using SQL Server 2008 Express and would like to do the following.

I have two tables, Ingredient and UnitOfMeasure. In Ingredient I have 5 columns: id, name, quantity, comment, uom_id. E.

    ID  NAME     QTY     COMMENT              UOM_ID
    1   Garlic   2.00    Finely chopped       1

In UnitOfMeasure I have 2 columns: id, name. E.G.

    ID  NAME
    1   Teaspoons

I've create a new column in Ingredient called "detail" and I would like to have it populated with a combination of all the other columns including the name from the unitOfMeasure table. E.G. of new Ingredient table required.

    ID  NAME    QTY    COMMENT         UOM_ID    DETAIL
    1   Garlic  2.00   Finely chopped  1         2 Teaspoons of Garlic (Finely chopped)

I've got some basic SQL which I've knocked up that looks like this.

    update INGREDIENT
    set detail = cast(quantity as varchar) + ' '  + 
             name + coalesce(' (' + COMMENT + ')', '')

It kind of works but has a few problems. Firstly the qty is coming out like this. 2.00 I want to be able to drop the .00 but there are circumstances when that may be 0.5 so I can't simply round the amount.

The second problem is when the comments field is blank I'm getting "()". If the comments filed is blank then I don't want to display the brackets.

The final problem is that I'm not sure how to do the join to the UnitOfMeasure table.

If you can solve even one of these issues I'd be very appreciative. Thanks.

Upvotes: 0

Views: 575

Answers (1)

rs.
rs.

Reputation: 27427

Try this

update i
set i.detail = cast(cast(i.quantity as int) as varchar) + ' '  + 
             name + coalesce(' (' + nullif(ltrim(rtrim(COMMENT)),'') + ')', '') 
from ingredient i
left outer join UnitOfMeasure m on i.UOM_ID = m.id

Upvotes: 2

Related Questions