To preserve NULLs, use CROSS JOIN … CASE: select a.ID, b.column_name , column_value = case b.column_name when ‘col1’ then a.col1 when ‘col2’ then a.col2 when ‘col3’ then a.col3 when ‘col4’ then a.col4 end from ( select ID, col1, col2, col3, col4 from table1 ) a cross join ( select ‘col1’ union all select ‘col2’ union … Read more
UPPER SELECT UPPER(LastName) + ‘, ‘ + FirstName AS Name FROM Person.Person
To answer your question about using the EXISTS keyword, here is an example query that uses an EXISTS predicate, based on the query as currently given in your question. SELECT t.* FROM tblTransaction t WHERE EXISTS ( SELECT 1 FROM tblTenantTransCode ttc JOIN tblCheckbookCode cc ON (cc.ID = ttc.CheckbookCode AND cc.Description=’Rent Income’) WHERE ttc.ID = … Read more
Don’t use the alias (P) in your WHERE clause directly. You can either use the same REPLACE logic again in the WHERE clause: SELECT Replace(Postcode, ‘ ‘, ”) AS P FROM Contacts WHERE Replace(Postcode, ‘ ‘, ”) LIKE ‘NW101%’ Or use an aliased sub query as described in Nick’s answers.
In SQL Server you can use YEAR, MONTH and DAY instead of DATEPART. (at least in SQL Server 2005/2008, I’m not sure about SQL Server 2000 and older) I prefer using these “short forms” because to me, YEAR(getdate()) is shorter to type and better to read than DATEPART(yyyy, getdate()). So you could also query your … Read more
select T.Column1, T.Column2, T.Column3, T.Column4, ( select count(*) from (values (T.Column1), (T.Column2), (T.Column3), (T.Column4)) as v(col) where v.col is not null ) as Column5 from Table1 as T
You need to use this: SELECT x.requestpayload.value(‘declare namespace s=”http://blah.ca/api”; (/s:validate-student-request/s:student-id)[1]’, ‘int’) AS studentid FROM xoutput x You need to put your XPath in ( … ) and add a [1] to simply select the first value of that sequence.
The using will take care of it for you. Under the hood, SqlConnection.Dispose() calls the SqlConnection.Close() method, and SqlCommand.Dispose() calls SqlCommand.Close(). As additional background, a using statement is syntactic sugar for a try … finally that disposes the IDisposable object in the finally.
You could use FOR XML PATH and STUFF to concatenate the multiple rows into a single row: select distinct t1.id, STUFF( (SELECT ‘, ‘ + convert(varchar(10), t2.date, 120) FROM yourtable t2 where t1.id = t2.id FOR XML PATH (”)) , 1, 1, ”) AS date from yourtable t1; See SQL Fiddle with Demo
Please check out the information schema. select * from MyDatabaseName.information_schema.columns order by table_name, ordinal_position