Number padding in TSQL

Sometime we need to pad leading zeros to numeric
values so that they can appear having same width
like 00001,0022,0934 etc.
Following code demonstrates the technique
to pad leading zeros in front of digits.

–Variable to hold max length
Declare @m_maxLength int

–Create a test table
Declare @m_testTable table (sampleValue int)

–Insert some sample values
Insert into @m_testTable values (1)
Insert into @m_testTable values (2)
Insert into @m_testTable values (19)
Insert into @m_testTable values (201)
Insert into @m_testTable values (20231)

–Calcualte the length of maximum number in the table
Select
@m_maxLength=Max(Len(convert(varchar,sampleValue)))
from @m_testTable

—Now select the values
Select
sampleValue,
Replicate(‘0′,@m_maxLength-Len(convert(varchar,sampleValue)))
+convert(varchar,sampleValue) as paddedValue
from @m_testTable

Aggregate concatenation in SQL Server 2005

In one of my previous posts, I talked about generating a comma separated list in TSQL. One of my readers has pointed out some interesting facts about issues related to this approach.

Following are the issues documented at MSDN

Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

Expression in ORDER BY clause causes self-referencing variables in the SELECT clause to evaluate only once rather than once per row

So what should we do now?
An alternative approach is to use For XML to generate comma separated list. Full working example is available at:

http://milambda.blogspot.com/2005/07/return-related-values-as-array.html
and
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx

Generating a comma separated list by an SQL Query

Today I am showing you a quick and easy way to concatenate rows of textual data in a single string. This is particularly useful when you need a comma separated list of items, products etc. and you don’t want to loop the whole dataset for performance reasons.

Following is the sample code to demonstrate this. Concept is very simple and anyone can easily understand it by running the following code.

Set Nocount On
Print ‘Start’
Print ‘Creating Sample Items table’
Go
CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO
Print ‘Inserting data in sample table’
Go

Insert into dbo.tblItems (ItemName) Values (‘Item1′)
Insert into dbo.tblItems (ItemName) Values (‘Item2′)
Insert into dbo.tblItems (ItemName) Values (‘Item3′)
Insert into dbo.tblItems (ItemName) Values (‘Item4′)
Go
Print ‘Following is the comma separated list of Items’
Go
Declare @m_ItemsList nvarchar(2000)
Select @m_ItemsList=”
Select @m_ItemsList=@m_ItemsList+ItemName+‘,’ from tblItems
Select Left(@m_ItemsList,Len(@m_ItemsList)-1)
Go
Print ‘Dropping sample table’
Drop table dbo.tblItems
Go
Print ‘Finished’