This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:
DECLARE @x TABLE
(
id INT,
row_num INT,
customer_code VARCHAR(32),
comments VARCHAR(32)
);
INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';
SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments
FROM @x AS x2 WHERE id = x.id
ORDER BY row_num
FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;
If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:
FOR XML PATH('')), 1, 1, '')
To this more elaborate approach:
FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')
(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)
When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.
I would suggest you to put a condition to check the existence of the table
If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end
You can even use below sql to create new blank table as the where 1=0 will always be false.
Note that it wont create any indexes from old_table into new_table.
SELECT *
INTO new_table
FROM old_table where 1=0
To capture errors, you can use TRY/CATCH
Best Answer
This seems like a simple self join:
The query assumes there are (max) 2 rows for any
idanddate_.If there are cases where people have entered without leaving, use
LEFTjoin instead. If there are people who have left without entering, useRIGHTjoin. If there might be both cases, useFULLjoin. ForRIGHTandFULL, you'll have to adjust theSELECTlist, usingleave.id, leave.date_andCOALESCE(enter.id, leave.id), COALESCE(enter.date_, leave.date_).