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

Joining to the Next Sequential Row

Because of the format problem, please see this article here. Sorry for this inconvenience. ;(

Get All Dates

When we develop application, we need all the days from the month sometimes. We are thinking of the way to create StoreProcedure to generate days using temp table and cursor. But using cursor will slow down our application process and we want faster way to get all days. So we think of another way to create one table which store all the days from 1-Jan of current year to 31-Dec. That’s also not easy to maintain this table and need to insert all days every year. Actually, I’m using this method in one of my application :D. Don’t try to use this method. This is very risky to get all days and insert all days. Here is very useful query statement to generate days from the month or the given months. I got this statement from one SQL forum but I forgot from which forum and who posted it. Anyway, this statement is really helpful to me and I’m sure you’ll also. Here’s the statement:

DECLARE @FirstDay smalldatetime, @NumberOfMonths int
SELECT @FirstDay = '20080301', @NumberOfMonths = 1
;WITH Days AS (
SELECT @FirstDay as CalendarDay
UNION ALL
SELECT DATEADD(d, 1, CalendarDay) as CalendarDay
FROM Days
WHERE DATEADD(d, 1, CalendarDay) < DATEADD(m, @NumberOfMonths, @FirstDay)
)
SELECT CalendarDay FROM Days

If you set 2 to @NumberOfMonths variable, you’ll get all the days from 2 months such as Mar and Apr.

Constant Table

In database development, sometimes we really need to display some constant data and don’t wanna let user change that constant data. But those data are not so many and don’t wanna create table. For this situation, we’ve one alternative way in SQL to create constant table. The trick is not so advanced but it’s really easy and we already know those statements. We just create one simple view using the following statements:

SELECT CustomerTypeID = 1, CustomerType = ‘Ordinary’
UNION ALL
SELECT CustomerTypeID = 2, CustomerType = ‘Executive’
UINON ALL
SELECT CustomerTypeID = 3, CustomerType = ‘Ultimate’

The above statement will give you one constant table for customer type. If you’ve more type, just edit the view and append another types. You can apply this in All SQL Version. :)

Add Serial Number to Our Query Result

Sometimes, we need to add serial number in our query result to show on the report. I’m not sure SQL 2000 has this feature or not but I never use that feature in SQL 2000. :D In SQL 2005, we’ve one function call ROW_NUMBER(). So we can display serial number in our query result. The example for that function is as follow:

SELECT ROW_NUMBER() OVER(ORDER BY customername) FROM customer WHERE customername LIKE ‘John%’

You can see detail description of this function at Microsoft MSDN Online
.