If you modify your use of For Xml Path, it will do the unescaping for you and you won’t need to resort to using the Replace function: , Stuff( ( Select ‘; ‘ + IsNull( D2.SelectedComments, ” ) From #StudentDetails As D2 Where D2.Stud_PK = A.Stud_PK And D2.CourseNo = A.CourseNo And D2.Section = A.Section For … Read more
You’d have to use dynamic SQL if you want to do it dynamically like that. Would mean anything you want to execute under the context of that DB, you’d need to include in the dynamic SQL statement too. i.e. assume you want to list all the tables in MainDB: This won’t work, as the USE … Read more
You can declare a label to do nothing. DECLARE @value INT IF @value IS NULL BEGIN no_op1: END
You don’t mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008) SELECT *, (SELECT AVG(c) FROM (VALUES(R1), (R2), (R3), (R4), (R5)) T (c)) AS [Average] FROM Request The 2005 version is a bit more tedious SELECT *, (SELECT AVG(c) FROM … Read more
For SQL Server you can use: select player, stuff((SELECT distinct ‘, ‘ + cast(score as varchar(10)) FROM yourtable t2 where t2.player = t1.player FOR XML PATH(”)),1,1,”) from yourtable t1 group by player
One way to do it if tagID values are known upfront is to use conditional aggregation SELECT TimeSeconds, COALESCE(MAX(CASE WHEN TagID = ‘A1’ THEN Value END), ‘n/a’) A1, COALESCE(MAX(CASE WHEN TagID = ‘A2’ THEN Value END), ‘n/a’) A2, COALESCE(MAX(CASE WHEN TagID = ‘A3’ THEN Value END), ‘n/a’) A3, COALESCE(MAX(CASE WHEN TagID = ‘A4’ THEN Value … Read more
It has a large impact. The Update lock takes an Update lock on the row, Intent update on the page and a shared lock on the table / database. This does not stop other queries from accessing the data within the table, since the locks on the page / database are purely share locks. They … Read more
It’s actually a collation issue. I can resolve it by changing these lines: CAST([type] collate database_default AS VARCHAR(255)) AS [type], CAST(type_desc collate database_default AS VARCHAR(255)) AS type_desc, CAST(create_date AS VARCHAR(255)) AS create_date, CAST(lock_escalation_desc collate database_default AS VARCHAR(255)) AS lock_escalation_desc The specific issue is that name is collated as Latin1_General_CI_AS, whereas the other 3 columns you … Read more
You are trying to perform an update, not inserting new rows. In order to do that, you will need to set identity_insert ON and copy the row you want to update to a new row with the new ID value, then delete the old row (assuming no FK is referencing it) Something along the lines … Read more
You can use a CTE: ;WITH RowNbrs AS ( SELECT ID , ROW_NUMBER() OVER (ORDER BY ID) AS RowNbr FROM MyTab WHERE a = b ) UPDATE t SET t.MyNo = 123 + r.RowNbr FROM MyTab t JOIN RowNbrs r ON t.ID = r.ID;