CODEWITHSUNDEEP
sql-servert-sql
user2371684
user2371684

Reputation: 1555

Format column result with % char in SQL Server

I have the following SQL code that uses the lag function.

SELECT SalesAmount
    ,lag(SalesAmount) OVER (
        ORDER BY DATE
        ) AS PreviousDaylSales
    ,(
        SalesAmount - (
            lag(SalesAmount) OVER (
                ORDER BY DATE
                )
            )
        ) AS Difference
    ,(
        SalesAmount - (
            lag(SalesAmount) OVER (
                ORDER BY DATE
                )
            )
        ) / (
        lag(SalesAmount) OVER (
            ORDER BY DATE
            )
        ) * 100 AS PercentChange
FROM Sheet1$

Can I format the alias column PercentChange values with a % char?

Upvotes: 0

Views: 55

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Yes, you can use square bracket []:

select . . . as [%Change]
from Sheet1$;

You can use sub-query to avoid repetition of same expression : 

SELECT t.*, (SalesAmount - PreviousDaylSales) AS AS Difference,
       CONCAT('%', SalesAmount - PreviousDaylSales / PreviousDaylSales * 100) AS [%Change]
FROM (SELECT SalesAmount, lag(SalesAmount) OVER (ORDER BY DATE) AS PreviousDaylSales
      FROM Sheet1$
     ) t;

Upvotes: 1

ThRnk
ThRnk

Reputation: 595

You can use CONCAT function.

select ...
CONCAT(...*100, '%') as PercentChange
    from Sheet1$

Upvotes: 0

Related Questions