Formatting Characters In SQL

If you want to format result values in SQL, you can use the following methods:

Let assume we have integer value variable.

DECLARE @DataValue AS int
SET @DataValue = 2

and wanna format as '00002'

There're 2 ways to format:

SELECT REPLICATE('0', 5 - LEN(@DataValue)) + CAST(@DataValue AS VARCHAR(MAX))

OR

SELECT RIGHT('00000' + CAST(@DataValue AS VARCHAR(max)), 5)

Row Data to Column Data

This is also one of my question in Experts-Exchange forum. I wanna display row data to in a columnar format. I’ve one tables which include three columns such as Sr, StartDate and EndDate. I wanna display count of Sr group by month depending on the StartDate and the resulted format is as follow:

Sr|Jan|Feb|Mar|..to..|Dec

The solution for this problem is as follow:

SELECT Sr, Jan = SUM(CASE WHEN MONTH(StartDate)=1 THEN 1 ELSE 0 END),
Feb = SUM(CASE WHEN MONTH(StartDate)=2 THEN 1 ELSE 0 END),
..
FROM Table
GROUP BY Sr

Really thanks for expert StephenCairns from Experts-Exchange for his solution which also opened my eyes on creating SQL queries later.

I also contributed some in experts exchange but my contributions are not very much so I won't post my contributions in my blog. :D

Display Different Values In Respective Columns

I haven’t updated my blog for quite long time. But today I’ve times to post some information related to the SQL query. When I prepare reports for one of the hospital in Myanmar, I’m facing some difficulties to display data and raised question in Experts-Exchange. This post is my re-collection of solutions from Experts-Exchange. And also thanks for the expert, angellll, who answer this solution for me. The problem is as follow:

I’ve one query which join 3 tables and produce different values in 2 columns.
Table A Contain (ID,SubID)
Data for Table A as example:
ID, SubID
1, 1
2, 7

Table B Contain (ID, Values)
Data for Table B as example:
ID, Values
1, 1000
2, 2500

Table C Contain (ID, SubID, Values)
Data for Table C as example:
ID, SubID, Values
1, 1, 100
1, 2, 150
1, 3, 20
2, 5, 0
2, 7, 150

I want to display as follow
ID, SubID, ID Values, SubID Values
1, 1, 1000, 100
1, 2, 0, 150
1, 3, 0, 20
2, 5, 0, 0
2, 7, 2500, 150

The solution for this problem is as follow:
SELECT a.id, a.subid, COALESCE(b.values,0) as [id values]
FROM tableA a
JOIN tableB b
ON a.ID = b.ID
RIGHT JOIN tableC c
ON c.id = a.id
AND c.subid = a.subid