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
Date to/from ID Conversion – TSQL
/*Convert DateTime to ID (yyyymmdd)*/
select replace(convert(char(10),getdate(),120),’-',”)
/*Convert DateID (yyyymmdd) to Date*/
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), 20080922))
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