Check Column exists in table, if not, add

Check For a Column Exists In A Table If Not Add

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME ='ColumnName')

BEGIN

ALTER TABLE TableName ADD ColumnName varchar(50) NULL

END

Coalesce

select StatusDes

from tblstatusmaster

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + StatusDes + ';' 
FROM  tblstatusmaster

SELECT @DepartmentName AS DepartmentNames

StatusDes

Scheduled

Registered

Scan Completed

Report Completed

In Progress

Report Despatched

DepartmentNames

Scheduled;Registered;Scan Completed;Report Completed;In Progress;Report Despatched;

Pivot Example

SELECT * FROM Sales

SELECT EmpId, [2005], [2006], [2007]

FROM (SELECT EmpId, Yr, Sales FROM Sales) AS s

PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

Output

EmpId	Yr	Sales
1	2005	12000
1	2006	18000
1	2007	25000
2	2005	15000
2	2006	6000
3	2006	20000
3	2007	24000

EmpId	2005	2006	2007
1	12000	18000	25000
2	15000	6000	NULL
3	NULL	20000	24000

Begin Tran and End Tran Example

BEGIN TRAN T1  

 ( code) 

  IF 

                            @@ERROR<> 0  

       BEGIN 

         RAISERROR ('ERROR OCCURS SELECTING.', 16, 1)  

         ROLLBACK  TRAN T1  

         RETURN 

       END 

  ELSE   

       BEGIN 

        COMMIT 

        TRAN T1  

       END   

Get Identity of a Table

SELECT IDENT_CURRENT(TableName')

CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS

The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

Difference b/w CUBE and ROLLUP:
- CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
- ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName

into #tempTable

UNION

select 'A', 2, 'a', 70 ,'harish'

UNION

select 'A', 3, 'a', 80 ,'kanchan'

UNION

select 'A', 4, 'b', 90 ,'pooja'

UNION

select 'A', 5, 'b', 90 ,'saurabh'

UNION

select 'A', 6, 'b', 50 ,'anita'

UNION

select 'B', 1, 'a', 60 ,'nitin'

UNION

select 'B', 2, 'a', 50 ,'kamar'

UNION

select 'B', 3, 'a', 80 ,'dinesh'

UNION

select 'B', 4, 'b', 90 ,'paras'

UNION

select 'B', 5, 'b', 50 ,'lalit'

UNION

select 'B', 6, 'b', 70 ,'hema'

select class, rollno, section, marks, stuName from #tempTable

output

class	rollno	section	marks	stuName
A	1	a	80	manoj
A	2	a	70	harish
A	3	a	80	kanchan
A	4	b	90	pooja
A	5	b	90	saurabh
A	6	b	50	anita
B	1	a	60	nitin
B	2	a	50	kamar
B	3	a	80	dinesh
B	4	b	90	paras
B	5	b	50	lalit
B	6	b	70	hema

WITH ROLLUP:

select class, section, sum(marks) [sum]

from #tempTable

group by class, section with ROLLUP

Output

class	section	sum
A	a	230
A	b	230
A	NULL	460
B	a	190
B	b	210
B	NULL	400
NULL	NULL	860

WITH CUBE:

select class, section, sum(marks) [sum]

from #tempTable

group by class, section with CUBE

Output

Output:

class   section sum

A       a       230

A       b       230

A       NULL    460  -- 230 + 230  = 460

B       a       190

B       b       210

B       NULL    400  -- 190 + 210 = 400

NULL    NULL    860  -- 460 + 400 = 860

NULL    a       420  -- 230 + 190 = 420

NULL    b       440  -- 230 + 210 = 440

COMPUTE & COMPUTE BY:
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.
The COMPUTE clause takes the following information:
- The optional BY keyword. This calculates the specified row aggregate on a per column basis.
- A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
- A column upon which to perform the row aggregate function.

select class, section, marks

from #tempTable

COMPUTE SUM(marks), AVG(marks)

select class, section, marks

from #tempTable

order by class

COMPUTE SUM(marks), AVG(marks) by class

select class, section, marks

from #tempTable

order by class, section

COMPUTE SUM(marks), AVG(marks) by class, section

GROUPING SETS:
SQL Server 2008 has a new GROUPING SETS operator which can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator.