Pivot (Dynamic)

USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_pivot] Script Date: 07/07/2010 14:26:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_pivot]
@query AS NVARCHAR(MAX),
@on_rows AS NVARCHAR(MAX),
@on_cols AS NVARCHAR(MAX),
@agg_func AS NVARCHAR(MAX) = N'MAX',
@agg_col AS NVARCHAR(MAX)

AS
DECLARE
@sql AS NVARCHAR(MAX),
@cols AS NVARCHAR(MAX),
@newline AS NVARCHAR(2);
SET @newline = NCHAR(13) + NCHAR(10);
-- If input is a valid table or view
-- construct a SELECT statement against it
IF COALESCE(OBJECT_ID(@query, N'U'),
OBJECT_ID(@query, N'V')) IS NOT NULL
SET @query = N'SELECT * FROM ' + @query;
-- Make the query a derived table
SET @query = N'(' + @query + @newline + N' ) AS Query';
-- Handle * input in @agg_col
IF @agg_col = N'*'
SET @agg_col = N'1';
-- Construct column list
SET @sql =
N'SET @result = ' + @newline +
N' STUFF(' + @newline +
N' (SELECT N'','' + '
+ N'QUOTENAME(pivot_col) AS [text()]' + @newline +
N' FROM (SELECT DISTINCT('
+ @on_cols + N') AS pivot_col' + @newline +
N' FROM' + @query + N') AS DistinctCols' + @newline +
N' ORDER BY pivot_col' + @newline +
N' FOR XML PATH('''')),' + @newline +
N' 1, 1, N'''');'
EXEC sp_executesql
@stmt = @sql,
@params = N'@result AS NVARCHAR(MAX) OUTPUT',
@result = @cols OUTPUT;
-- Create the PIVOT query
SET @sql =
N'SELECT *' + @newline +
N'FROM' + @newline +
N' ( SELECT ' + @newline +
N' ' + @on_rows + N',' + @newline +
N' ' + @on_cols + N' AS pivot_col,' + @newline +
N' ' + @agg_col + N' AS agg_col' + @newline +
N' FROM ' + @newline +
N' ' + @query + @newline +
N' ) AS PivotInput' + @newline +
N' PIVOT' + @newline +
N' ( ' + @agg_func + N'(agg_col)' + @newline +
N' FOR pivot_col' + @newline +
N' IN(' + @cols + N')' + @newline +
N' ) AS PivotOutput;'
EXEC sp_executesql @sql;
GO

Disabling (or Enabling) SQL Server Agent Jobs

http://www.mssqltips.com/tip.asp?tip=1400

Current login user

select @@IDENTITY ,@@CONNECTIONS,CURRENT_USER, SUSER_SNAME(),@@version

VIEW_METADATA

1) Minor Security nit: because you do not want the client software to know the names of your base tables.

and

2) Client App problem: The client app does not behave correctly unless you turn this on. This could happen for instance, because you restructured a former base table into a new structure and organization in your DB and used a view of the same name make the schema appear the same to the app. But the app is trapping the base-table name of the columns, whic is no longer correct unless you turn VIEW_METADATA on.

also:

3) Possibly to conceal base tables from some reporting packages that insist on trying to copy the whole base table to the client. Make them think that the view is the base table so that they will only try to copy the view's data to the client

SIGN Function

/*
SIGN function returns the sign of the supplied value X as -1, 0, or 1,
depending on whether the value X is negative, zero, or positive, respectively.
*/
select SIGN(0);
select SIGN(-3);
select SIGN(3);
/*
mysql> select SIGN(0);
+---------+
| SIGN(0) |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)

mysql> select SIGN(-3);
+----------+
| SIGN(-3) |
+----------+
| -1 |
+----------+
1 row in set (0.00 sec)

mysql> select SIGN(3);
+---------+
| SIGN(3) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)


*/

Updates the metadata for the specified non-schemabound view

SELECT N'EXEC sp_refreshview '
+ QUOTENAME(VIEW_NAME, '''') + ';' AS cmd
FROM (SELECT QUOTENAME(TABLE_SCHEMA)
+ N'.' + QUOTENAME(TABLE_NAME) AS VIEW_NAME
FROM INFORMATION_SCHEMA.VIEWS) AS V
WHERE OBJECTPROPERTY(OBJECT_ID(VIEW_NAME), 'IsSchemaBound') = 0;

Columns_Updated()

Value 1 2 4 8 16 32 62 128
Column 1 2 3 4 5 6 7 8
On/Off 0 1 0 1 0 0 0 0