Archive for September, 2008

T-SQL String Concatenation using FOR XML PATH(”)

Sometimes we need to convert (concatenate) string column values for a given group into a
comma(or any character) separated list of values.

eg:
I have this:

I want this:

here is one way of doing this in T-SQL using FOR XML PATH(”) (only works with SQL Server 2005 and above)

select NoteID, STUFF(
	(SELECT  ',' + a.Comments  AS [text()]
	from Notes  a
	where a.NoteID = b.NoteID
	Order by a.Comments
	for xml PATH('')),1,1,''	) AS Comments_Concatenated
 from Notes b
group by NoteID
ORDER BY NoteID

or convert all rows into csv..

SELECT 'MP' NAME
into #tt1
UNION
SELECT 'BP' NAME
UNION
SELECT 'HP' NAME

select name from #tt1

declare @strTemp as varchar(100)

Select @strTemp = STUFF(
	(SELECT  ',' + a.Name  AS [text()]
	from #tt1  a
	for xml PATH('')),1,1,'' )  

print @strTemp

STUFF function removes the leading comma

Tags: , ,

Monday, September 29th, 2008 SQL Server No Comments

Date to/from ID Conversion – TSQL

/*Convert DateTime to ID (yyyymmdd)*/
select convert(char(10),getdate(),112)

/*Convert DateID (yyyymmdd) to Date*/
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), 20080922))

Tags: , , ,

Saturday, September 27th, 2008 SQL Server No Comments

Help for Hierarchies – Dimensional Modelling

here is a good article by Ralph Kimball on managing ragged/variable-depth hierarchies in a data warehouse

http://www.dbmsmag.com/9809d05.html

And here is the design tip that follows on from the above article

Kimball Design Tip #17: Populating Hierarchy Helper Tables

Tags: ,

Tuesday, September 9th, 2008 DW No Comments