Concatenate derived currency symbol and value in SQL

Question

I am using MSSQL (2012) and need to return a numeric value that is cast in decimal to include the relevant currency symbol. I have returned the currency symbol using CASE as follows:

SELECT (CASE (Currency_ID) WHEN (1) THEN '£' WHEN (2) THEN '$' END) AS Currency_Symbol

I would then like to concatenate this with the numeric value, which I have had to CAST to correct the decimal places as follows:

SELECT CAST(Price AS decimal(10,2)) AS Unit_Price

However, I seem unable to use CONCAT or any other method to do this, and as I understand it I won't be able to reference the 'Currency_Symbol' value in the same SELECT statement.

Any ideas?

Answer 1

declare @Currency_ID int, @dec decimal(8,2)
set @Currency_ID = 1;
set @dec = 123.45
SELECT (CASE (@Currency_ID) WHEN (1) THEN '£' WHEN (2) THEN '$' END) + cast(@dec as varchar)

Modified .. If you are using a table and the Currency_ID and Unit_Price is from a table

SELECT (CASE (Currency_ID) WHEN (1) THEN '£' WHEN (2) THEN '$' END) + cast(Unit_Price as varchar) FROM SOMETABLE