TSQL Reference Code

DYNAMIC PIVOT & UNPIVOT

DYNAMIC PIVOT

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AssignmentName)
from yourtable
FOR XML PATH(''), TYPE
                                  ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT StudentName, ' + @cols + ' from
(
     select StudentName, AssignmentName, grade
     from yourtable
) x
pivot
(
     min(grade) for assignmentname in (' + @cols + ')
) p '

execute(@query)

​http://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns

DYNAMIC UNPIVOT

DECLARE @colsUnpivot AS NVARCHAR(MAX),
                   @query AS NVARCHAR(MAX)

select @colsUnpivot

= stuff((SELECT','+quotename(C.name)
             FROM sys.columns c
            WHERE c.object_id = OBJECT_ID('dbo.cal')   for xml path('')), 1, 1, '')
   set @query

= 'select data, datename(dw, data) dayname
    from cal
    unpivot
    (
        data for d in ('+ @colsunpivot +')
    ) u'
exec sp_executesql @query;​

http://dba.stackexchange.com/questions/48393/passing-column-names-dynamically-to-unpivot

FIRST & LAST DAYS OF MONTH

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),101) ,
'Last Day of Previous Month'

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AS Date_Value,
'First Day of Current Month' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),GETDATE(),101) AS Date_Value, 'Today' AS Date_Type

UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) ,
'Last Day of Current Month'


UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101) ,
'First Day of Next Month'

http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/_

DATE FORMATS IN TSQL

Mon DD YYYY HH:MIAM (or PM)

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Jan 1 2005 1:29PM

MM/DD/YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
11/23/1998

DD/MM/YY

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
19/02/72

DD/MM/YYYY

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
19/02/1972

To see more conversion options follow the link below.
http://www.sql-server-helper.com/tips/date-formats.aspx

CONCATENATING STRINGS - XML PATH

​SELECT CAT.Name AS [Category],
            STUFF(( SELECT ',' + SUB.Name AS [text()]
-- Add a comma (,) between each value
FROM Production.ProductSubcategory SUB
WHERE SUB.ProductCategoryID = CAT.ProductCategoryID
FOR XML PATH('') — Select it as XML
), 1, 1, '' )
-- To remove the first character (,) from the result
AS [Sub Categories]
FROM Production.ProductCategory CAT

http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/_

SPLIT FUNCTION CODE

CREATE FUNCTION [dbo].[fn_Split]
(
     @String nvarchar(4000)
    ,@Delimiter nvarchar(10)
)
RETURNS TABLE AS
RETURN (
     SELECT
           Measure = LTrim(RTrim(Convert(nvarchar(4000), SubString(@String, n.Number,                                                  CharIndex(@Delimiter, @String + @Delimiter, n.Number) - n.Number))))

          ,StartPosition = n.Number
     FROM DBAUtility.dbo.Numbers AS n
     WHERE n.Number <= Convert(int, Len(@String))
          AND SubString(@Delimiter + @String, n.Number, LEN(@Delimiter)) = @Delimiter 

-- Removing this line helps to clarify how this works
)

How to use in WHERE Clause

WHERE
and c.Column1 in (select measure from dbo.fn_Split(@Parameter,','))

How to use in TEMP Table


SELECT measure
INTO #BusinessUnits
FROM dbo.fn_Split(@BusinessUnit,',')