This solution does not require a variable and it also allows you to do joins against other tables within one statement. The substring function removes the leading comma. Download the presidents table here. SELECT ColumnList = SUBSTRING( (SELECT ‘,’ + ISNULL(President,”) FROM dbo.Presidents FOR XML PATH(”)) ,2,8000) Using a common table expression (CTE) you can [...]
Archive of posts filed under the SQL category.
SQL Cast Date
Using SQL Server 2008 This first option of removing the date uses the SQL Server 2008 method. This will return only the date or only the time: — remove the time SELECT CAST(GETDATE() AS date) — 2009-07-12 — remove the date SELECT CAST(GETDATE() AS time) — 08:46:25.8130000 If you’re not working with SQL Server 2008, [...]
Insert Carriage Return Line Feed to String
Here is a clean and efficient way to embed carriage returns into a string. I prefer this way instead of concatenating the entire CHAR(13)+CHAR(10) to the end of every string. This replace function also comes in handy in most instances where you need to append strings. declare @Note varchar (200) SET @Note = ‘Line One.[crlf];Line [...]
SQL Server Pagination using CTE and RowNumber
This is a succinct example of a pagination implementation. The ROW_NUMBER() function is very fast and very useful. The CTE’s (Common Table Expressions) are novel, however where the rubber meets the road temp tables are usually far more efficient. SET @rowsPerPage = 10 SET @pageNum = 3 With SQLPaging AS ( Select Top(@rowsPerPage * @pageNum) [...]
