Select + + -pizzaparty!!, only if we skip the null var NOTE: the Concat function exists in SQL Server 2012 and newer.įor example: declare varchar(10) = 'pizza' The concat function takes n arguments and concatenates them in the sequence they were passed. Select coalesce(45,12.2,6) as 'Col1' into CoalesceTest2 Įxec sp_help 'CoalesceTest1' -Col1 is intĮxec sp_help 'CoalesceTest2' -Col1 is numericīehavior of Null in Concat vs + (String) ConcatenationĬonsider 2 different ways of concatenating – the Concat function and the + string concatenation operator. select coalesce(12,'pizza',44) as 'Col1' into CoalesceTest1 If you pass an array of integers and floats you will get numeric. This might not always be what you want because if you pass bits you might get integers. It will take the data type from the first non-null value passed and use that for the table definition.
Select isnull('twelve','unknown') as 'Col1' into IsNullTest2 Įxec sp_help 'IsNullTest1' -Col1 is intĮxec sp_help 'IsNullTest2' -Col1 is varchar select isnull(12,'unknown') as 'Col1' into IsNullTest1 This example shows the data type changing depending on how the functions are evaluated. Select ProductID, Name, coalesce(Color, 'Unknown') as 'Color'Įxec sp_help 'Production.Product_isnull' -Color is not nullableĮxec sp_help 'Production.Product_coalesce' -Color is nullable Select ProductID, Name, isnull(Color, 'Unknown') as 'Color' You might expect the column data type and nullability to be the same but they are different! -IsNull ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedenceĬonsider loading a table using these functions. From BOL:ĭata type determination of the resulting expression is different. Select ProductID, Name, Color, isnull(Color, 'Unknown') as 'Color-IsNull', coalesce(Color, 'Unknown') as 'Color-Coalesce'Īnother variation has to do with the data types of the arguments passed. Therefore whenever possible choose coalesce for portability concerns. NOTE: IsNull is proprietary of T-SQL while Coalesce is ANSI standard. IsNull is a function and will only be evaluated once. This means the arguments passed here will be evaluated multiple times. While coalesce is somewhat more robust don’t forget that at the heart it is simply using a case statement switch to check if the value is null then return control flow. The Coalesce function takes n arguments and returns the first non-null value passed to it. The IsNull function in SQL Server takes 2 arguments – one for the expression to evaluate for null and the other that it will return if the first argument is null. Look at the following scenarios of how null marks are treated different than the unsuspecting eye might see. Sometimes it is good to revisit the basics of how they are treated in basic system functions and T-SQL because they can be tricky. I don't understand why this is, because this is the alias I have given it and want to reference it for things like joins further in the pipeline.Null marks are a fact of life in relational databases. The issue I have is how can I insert a separator in the concatenation part that creates NewColumn in the pivot.Īlso when I then run a select * from pivotexample2 query, it says Invalid object name 'pivotexample2'. The distinct values in the category column can change so I needed a dynamic solution (as above). SELECT = + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
Which I can convert into wide format: DECLARE AS VARCHAR(MAX)='' Insert into #temp values ('2', 'dinner', 'no') Insert into #temp values ('2', 'lunch', 'yes') Insert into #temp values ('2', 'breakfast','no') Insert into #temp values ('1', 'dinner','yes') Insert into #temp values ('1', 'lunch','no') Insert into #temp values ('1', 'breakfast','yes')
SQL SERVER CONCAT CODE
The code was heavily influenced by: SQL Server dynamic PIVOT query? create table #temp I have a table in long format which I convert to wide format dynamically.