Friday, July 5, 2013

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.