SQL Tips

Today I got a Question on PIVOT function. I just learnt it today. This link helped me to understand it. Syntax as provided is very clear

SELECT columns
FROM table
PIVOT
(
    Aggregate Function(Measure Column)
    FOR Pivot Column IN ([Pivot Column Values])
)
AS Alias

Aggregate functions Example - Avg, MIN, MAX, SUM
Modified the example for all entries in column (Variable)
Example
SELECT *
FROM #temp123
PIVOT
(
      SUM(VaribleValue)
      FOR [Variable]
IN ([Sales],[Expenses],[Taxes],[Profit])
)
AS p

Modified code

DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
    '],[' + t2.Variable
    FROM #temp123 AS t2
    ORDER BY '],[' + t2.Variable
    FOR XML PATH('')
    ), 1, 2, '') + ']'
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT * FROM #temp123
PIVOT
(
    SUM(VaribleValue)
    FOR Variable IN
    ( '+
        @cols +' )
) AS pvt'
EXECUTE(@query)

SQL Tip #2
Differences between ISNULL and COALESCE 

• ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters
• COALESCE basically translates to CASE expression and ISNULL is a built-in implemented in the database engine
• Different Outputs as provided below (SQL 2008 R2 I tried)

 SELECT ISNULL(NULL, 1) -- Output 1
 SELECT COALESCE(NULL, 1) -- Output 1
 SELECT ISNULL(NULL, NULL) -- Output NULL
 SELECT COALESCE(NULL, NULL, CAST(NULL as int)) -- Output NULL
 SELECT COALESCE(NULL, NULL) -- Output - Errors
Reference