Percentage Format in T-SQL

There is no function to present the percentage format in T-SQL, something like 17.25%. There are various ways to do this. However, for reporting purpose, the easiest way might be:

CONVERT(VARCHAR, Result_From_Calc)+'%'

Following is an example how to add Total row at bottom of the selected dataset as will as add percentage column for each of rows.

The original dataset is:

SELECT Leg_Id
, Statute_Miles
FROM dw_Legs
GO

The final script is as follows:

WITH Totaling
AS
(
SELECT SUM(Statute_Miles) AS Milage
FROM dw_Legs
)
SELECT Leg
, Milage
, CONVERT(VARCHAR, Milage*100/(SELECT Milage FROM Totaling))+'%' AS Percentage
FROM
(
SELECT CONVERT(VARCHAR, Leg_Id) AS Leg
, Statute_Miles AS Milage
FROM dw_Legs
UNION
SELECT 'Total' AS Leg
, Milage
FROM Totaling
) AS Dataset
GO

Explaination:

1. Total milage will be used more than once, so it has been in put into WITH clause.
2. The reason convert Leg_Id into VARCHAR is because word ‘Total’ is a VARHCAR. Else the UNION won’t work.
3. Make sure there is only one row being selected into WITH clause. That is where people usually create a bug.

Following is the result: