Dynamic Crosstab in SQL Server

create PROC sp_CrossTab
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(500), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(1000), -- Destination columns (on columns)
@sumcol AS sysname = NULL, -- Data cells
@whereclause as nvarchar(500)= NULL -- where clause
AS
DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END


CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table

print @keyssql
EXEC (@keyssql)


DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @key+ ']'

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE
if @whereclause is not null
SET @sql = @sql+ 'WHERE ' + @whereclause + ' '+ @NEWLINE
SET @sql = @sql+ 'GROUP BY ' + @onrows + @NEWLINE
SET @sql = @sql+ 'ORDER BY ' + @onrows

PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)

---Reference SQL Magazine
----------------------------------------
Eg:

Use Northwind
go
EXEC sp_CrossTab
@table = 'Orders',
@onrows = 'MONTH(OrderDate)',
@onrowsalias = 'OrderMonth',
@oncols = 'YEAR(OrderDate)'


USE pubs
go
EXEC sp_CrossTab
@table = 'sales',
@onrows = 'stor_id',
@oncols = 'YEAR(ord_date)',
@sumcol = 'qty'

How to change the collation against all the tables and columns within a database

Onlyway is to create alter statement from INFORMATION_SCHEMA.COLUMNS.

SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE SQL_Latin1_General_CP850_BIN '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END

FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')

A good source for SQL Server DBA

Core DBA
and
Transactional SQL


thanks to Randy Dyess for the Article.

SQLServer -Store Multiple Values in a Single Value

Question(http://www.dbforums.com/showthread.php?t=1201269)
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

Answer

--Yes its possible using bitwise operation and (&) operator


eg:
--creating table

create table #c
(
CRITERIAID int,
CRITERIA varchar(100),
CRITERIAVALUE int
)
go
create table #search
(
userid int,
searchSumValue int
)
go
---insert sample records
set nocount on
insert into #c values (1,'Age',2)
insert into #c values (2,'sex',4)
insert into #c values (3,'Grade',8)
insert into #c values (4,'Locale',16)
insert into #c values (5,'Region',32)
go

----sample data in search table

insert into #search select 1,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,16)
insert into #search select 2,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (4,8,32)
insert into #search select 3,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,8,32)
insert into #search select 4,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,8,16,32)
insert into #search select 5,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2)
insert into #search select 6,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (16,32)
go

-----------select multiple values using bitwise (&) operator------

--userid 1 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=1
--userid 2 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=2

--userid 3 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=3
--userid 4 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=4

--userid 5 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=5
--userid 6 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=6

Indian Yoga Vs Irish Yoga

For all you yoga fans out there......

Indian Yoga














Irish Yoga