0 How to Sort Alphanumeric Data in SQL

Introduction If your SQL query is not returning the result-set in the order you are expecting, this article may be helpful to fix the issue. Background We all know that the ORDER BY keyword is used to sort a result-set by a specified column. It works great for most of the cases. But, for alphanumeric data, it may not return the result-set that you will be expecting. This article explains how this can be fixed easily. Using the Code Step 1 I have created a table named “Test” with two columns, as shown below: The following data has been added to the “Test” table: The “Order By” in the following SQL query...

0 Concatenate row values T-SQL

DECLARE @Reviews TABLE(         ReviewID INT,         ReviewDate DATETIME) DECLARE @Reviewers TABLE(         ReviewerID   INT,         ReviewID   INT,         UserID INT) DECLARE @Users TABLE(         UserID  INT,         FName  VARCHAR(50),         LName VARCHAR(50) ) INSERT INTO @Reviews SELECT 1, '12 Jan 2009' INSERT INTO @Reviews SELECT 2, '25 Jan 2009' INSERT INTO @Users SELECT 1, 'Bob', '' INSERT INTO @Users SELECT 2, 'Joe', '' INSERT INTO @Users SELECT 3, 'Frank',...

0 COALESCE

Table : People Name Peter Paul Mary   DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People Just some explanation (since this answer seems to get relatively regular views): Coalesce is really just a helpful cheat that accomplishes two things: 1) No need to initialize @Names with an empty string value. 2) No need to strip off an extra separator at the end. The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions: DECLARE...