There is an equivalent. It’s called a CASE statement. There are two forms of CASE: Simple CASE: CASE search-expression WHEN expression [, expression [ … ]] THEN statements [ WHEN expression [, expression [ … ]] THEN statements … ] [ ELSE statements ] END CASE; Searched CASE: CASE WHEN boolean-expression THEN statements [ WHEN … Read more
This generates a query with all columns ordered alphabetically in the select statement. DECLARE @QUERY VARCHAR(2000) DECLARE @TABLENAME VARCHAR(50) = ‘<YOU_TABLE>’ SET @QUERY = ‘SELECT ‘ SELECT @QUERY = @QUERY + Column_name + ‘, ‘ FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME ORDER BY Column_name SET @QUERY = LEFT(@QUERY, LEN(@QUERY) – 4) + ‘ FROM ‘+ … Read more
Use the HSTORE column type. HSTORE stores key/value pairs. You can use null values if you only care about checking if a key exists. See https://www.postgresql.org/docs/current/static/hstore.html. For example, to ask Is ‘x’ in my hstore?, do CREATE EXTENSION HSTORE; –create extension only has to be done once SELECT * FROM ‘x=>null,y=>null,z=>null’::HSTORE ? ‘x’; I believe … Read more
It is excellent for staging mock tables when testing. For example, if your source tables contain millions of records and you want to test a small subset of data, you can use synonyms to re-direct the source table to a smaller table you control so you can stage various scenarios. In this way, you can … Read more
#tablename is a physical table, stored in tempdb that the server will drop automatically when the connection that created it is closed, @tablename is a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable. You can only add a (non PK) index to a … Read more
You need a function to do the concatenation. Microsoft Access condense multiple lines in a table Example using your data: Select T.ColumnA , GetList(“Select ColumnB From Table1 As T1 Where T1.ColumnA = ” & [T].[ColumnA],””,”, “) AS ColumnBItems From Table1 AS T Group By T.ColumnA;
An index cannot safely be used in this case. A naive implementation would transform this: … WHERE word LIKE ‘search_string%’ into … WHERE word >= ‘search_string’ AND word < ‘search_strinh’ by incrementing the last character of the search string. The greater-than and less-than operators can use an index, where LIKE cannot. Unfortunately, that won’t work … Read more
I don’t know if this qualifies as “more elegant” but it is written in a different manner than Cybernate’s solution (although it is essentially the same) WITH window_table AS ( SELECT s.*, sum(volume) OVER previous_rows as total FROM stuff s WINDOW previous_rows as (ORDER BY priority desc ROWS between UNBOUNDED PRECEDING and CURRENT ROW) ) … Read more
It indicates a “nationalized” a.k.a. unicode string constant. http://support.microsoft.com/kb/239530 When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N, as documented in the SQL Server Books Online topic “Using Unicode Data”. http://msdn.microsoft.com/en-us/library/aa276823%28SQL.80%29.aspx nchar and nvarchar Character data types that are either fixed-length (nchar) or variable-length … Read more