Concatenate Rows

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. [cc lang=”sql”] SELECT ColumnList = SUBSTRING( (SELECT ‘,’ + ISNULL(President,”) FROM dbo.Presidents FOR XML PATH(”)) ,2,8000) [/cc] Using a common table expression (CTE) you can also recursively union the CTE with itself in order to generate a comma delimited list. This is a useful function for a CTE. The CTE itself does concatenate more than what is needed however it’s still relatively clean. Download the presidents table here. [cc lang=”sql”] ;WITH PresidentList (PresidentNumber, President) AS ( SELECT 1 , CAST(” AS VARCHAR(max)) UNION ALL SELECT cte.PresidentNumber + 1 ,cte.President + pl.President + ‘, ‘ FROM ( SELECT RowNum = Row_Number() OVER (ORDER BY PresidentNumber) ,President FROM dbo.Presidents ) pl JOIN PresidentList cte ON pl.RowNum = cte.PresidentNumber ) SELECT TOP 1 President FROM PresidentList ORDER BY PresidentNumber DESC [/cc] The next two examples require a variable, and when using a variable you cannot display any other columns from the table in the result set. There is a misnomer floating around much of the TSQL community with regards to concatenating strings using COALESCE. In actuality, this has nothing to do with COALESCE. COALESCE is simply used to replace a potential NULL value in one of the rows. Here is the code without using a COALESCE. [cc lang=”sql”] DECLARE @ColumnList varchar(8000) SET @ColumnList = ” — Set this to empty […] Continue reading ...

Using the Merge Statement to populate a historical table with Effective Dating

One of my favorite uses for the MERGE statement introduced in SQL Server 2008 is the updating of a historical table. With versions prior to 2008 this operation had to be performed in two separate statements. Merge helps us to streamline the process. The advantage to the database engine when using a Merge statement is in it’s minimal locking. Prior to merge, we had to update the record if it existed. And if it did not exist, we inserted the record. Long story short, it was double the amount of locking before the optimizer knew which rows to exclusively lock. To understand what’s going on in the statement below, you have to read starting from the MERGE statement. This is where the initial updating is taking place first. As you scroll down, you will see the OUTPUT clause. This actually outputs the results of the MERGE statment. You can think of this output as a virtual table named “merged”. The $action variable is an intrinsic column (if you will), that contains the action that row played during the MERGE. Lastly, if you scroll to the top, you will find the insert statement that finally inserts the new “current” record for the corresponding “expired” record we just retired. This was the same row we used for the criteria to UPDATE the expired row, however we did not use any of it’s values. [cc lang=”sql”] DECLARE @Now datetime = GETDATE() DECLARE @EffToDate datetime = ‘2079-06-06T00:00:00.000’ DECLARE @JobID int = 1 — This […]

Using the Merge Statement’s OUTPUT Clause to Match Inserted IDENTITY Fields with Source Table Fields

With this project I needed to insert data into table1 from table2 while simultaneously retrieving the identity field of table1, then insert that identity into table3 which mapped to table2 on a key that table1 did not have. The OUTPUT clause when used with the input statment would not facilitate this because it could not return the identity and the source key in the same row. The result was to use the SQL Server 2008 Merge statement with the output clause. Traditionally I would have looped the insert one record at a time and returned back both the values into variables, then inserted the identity into table3 by joining the keys on table2 and table 3. [cc lang=”sql”] CREATE TABLE #MatchIDs ( Table_1_EmployeeID int, Table_2_EmployeeID int ) INSERT INTO #MatchIDs ( Table_1_EmployeeID, Table_2_EmployeeID ) SELECT mrg.Table_1_EmployeeID ,mrg.Table_2_EmployeeID FROM( MERGE dbo.Table_1 t1 USING dbo.Table_2 t2 ON 1 = 0 WHEN NOT MATCHED THEN INSERT ( FirstName ) VALUES ( t2.FirstName ) OUTPUT $action ,t2.EmployeeID ,t1.EmployeeID ) AS mrg ( output_action ,Table_1_EmployeeID ,Table_2_EmployeeID ) WHERE mrg.output_action = ‘INSERT’ [/cc] The result is a statement that actually fools the MERGE by intentionally not matching any rows and just inserting all the records into the table. Continue reading ...

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: [cc lang=”sql”] — remove the time SELECT CAST(GETDATE() AS date) — 2009-07-12 — remove the date SELECT CAST(GETDATE() AS time) — 08:46:25.8130000 [/cc] If you’re not working with SQL Server 2008, you have to improvise.   Cast Date With No Time Using Floor This example removes the time from the date time by setting it to the beginning of the day. [cc lang=”sql”] — Get the current day with no time — 2009-06-29 00:00:00.000 SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) — Get the next day — 2009-06-30 00:00:00.000 SELECT CAST(CEILING (CAST(GETDATE() AS float)) AS datetime) [/cc] Cast Date with No Time Using Convert Using convert you can remove the time for display purposes, usually for reporting or the front end. [cc lang=”sql”] — Month first SELECT CONVERT(varchar(12),GETDATE(), 101) — 06/29/2009 SELECT CONVERT(varchar(12),GETDATE(), 110) — 06-29-2009 SELECT CONVERT(varchar(12),GETDATE(), 100) — Jun 29 2009 SELECT CONVERT(varchar(12),GETDATE(), 107) — Jun 29, 2009 — Year first SELECT CONVERT(varchar(12),GETDATE(), 102) — 2009.06.29 SELECT CONVERT(varchar(12),GETDATE(), 111) — 2009/06/29 SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629 — Day first SELECT CONVERT(varchar(12),GETDATE(), 103) — 29/06/2009 SELECT CONVERT(varchar(12),GETDATE(), 105) — 29-06-2009 SELECT CONVERT(varchar(12),GETDATE(), 104) — 29.06.2009 SELECT CONVERT(varchar(12),GETDATE(), 106) — 29 Jun 2009 — Time only SELECT CONVERT(varchar(12),GETDATE(), 108) — 07:26:16 SELECT CONVERT(varchar(12),GETDATE(), 114) — 07:27:11:203 [/cc] Roll Your Own Date To roll your own date format, use the datename function. This is also […]