SQL with Manoj

Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

Convert multiple Rows into a single column

August 17, 2010 2 comments

Just replied an answer on a SQL forum (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148860), so thought to post this as well for future reference.

How will you output the following record set:
123456788  City     399.99
123456788  County   499.99
123456788  Flood    299.99
123456788  Hazzard  199.99
123456789  City     333.99
123456789  County   444.99
123456789  Flood    222.99
123456789  Hazzard  111.99

... into following pattern:

id            Hazzard     Flood      City       County
123456788     199.99      299.99     399.99     499.99
123456789     111.99      222.99     333.99     444.99

The following query will do it and convert the rows into columns:

select 123456788 as id,  'Hazzard' as des, 199.99 as val
into #tempTable
union
select 123456788, 'Flood', 299.99 union
select 123456788, 'City', 399.99 union
select 123456788, 'County', 499.99 union
select 123456789, 'Hazzard', 111.99 union
select 123456789, 'Flood', 222.99 union
select 123456789, 'City', 333.99 union
select 123456789, 'County', 444.99
select * from #tempTable

select a.id as id, a.val as Hazzard, b.val as Flood, c.val as City, d.val as County
from #tempTable a,#tempTable b,#tempTable c, #tempTable d
where a.id=b.id and b.id=c.id and c.id=d.id
and a.des='Hazzard' and b.des='Flood' and c.des='City' and d.des='County'

drop table #tempTable

The above select code is with a simple old style where clause with multiple tables joining. The code could be also converted to self-joins.

Categories: JOINS, SQL Tips Tags: Convert Rows to Columns, SELF JOIN

Using OVER and PARTION BY clauses with Aggregate Functions in SQL Server

August 10, 2010 5 comments

In my [previous post] we learned about MS SQL Server 2005 Ranking functions.

Here we’ll see more scenarios where they can be used. We can use them with CTE i.e. Common Table Expressions, again a new gem with ver. 2005.

with myCTE as (
    select row_number() over(partition by class order by marks desc) as [RowNumber],
    class, marks, stuName from #tempTable)
select * from myCTE 
where RowNumber < 5

This gives following result:
row_num class marks name
1       A     90    pooja
2       A     90    saurabh
3       A     80    kanchan
4       A     80    manoj
1       B     90    paras
2       B     80    dinesh
3       B     70    hema
4       B     70    kapil

A CTE (Common Table Expression) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

More on CTE: http://msdn.microsoft.com/en-us/library/ms190766.aspx

The above mentioned PARTITION BY clause can also be used with SQL aggregate functions, like: AVG, COUNT, MIN, MAX, etc.

Here is an example:

select class, stuName, marks,
    AVG(marks) over(partition by class) AS [avg],
    SUM(marks) over(partition by class) AS [sum],
    MIN(marks) over(partition by class) AS [max],
    MAX(marks) over(partition by class) AS [min],
    COUNT(marks) over(partition by class) AS [count],
    STDEV(marks) over(partition by class) AS [ST_Dev],
    VAR(marks) over(partition by class) AS [VAR]
from #tempTable

This gives following result:
class name     marks AVG  SUM  MIN  MAX  COUNT STDEV   VAR
A     anita    50    76   460  50   90   6     15.0554 226.6667
A     harish   70    76   460  50   90   6     15.0554 226.6667
A     kanchan  80    76   460  50   90   6     15.0554 226.6667
A     manoj    80    76   460  50   90   6     15.0554 226.6667
A     pooja    90    76   460  50   90   6     15.0554 226.6667
A     saurabh  90    76   460  50   90   6     15.0554 226.6667
B     kamar    50    67   470  50   90   7     14.9603 223.8095
B     lalit    50    67   470  50   90   7     14.9603 223.8095
B     nitin    60    67   470  50   90   7     14.9603 223.8095
B     hema     70    67   470  50   90   7     14.9603 223.8095
B     kapil    70    67   470  50   90   7     14.9603 223.8095
B     dinesh   80    67   470  50   90   7     14.9603 223.8095
B     paras    90    67   470  50   90   7     14.9603 223.8095

I’ve rounded the last 2 column values to 4 decimal places.

Categories: SQL Server 2005 Tags: Aggregate Functions, MAXCONCURRENT, OVER clause, PARTITION BY

Ranking Functions | ROW_NUMBER, RANK, DENSE_RANK, NTILE

August 9, 2010 9 comments

SQL Server 2005 provides functionality for using Ranking Functions with your result set. One can select a number of Ranking algorithms which are applied to a column of your table that you want to classify in a scope of your executing query. This feature is Dynamic and upon change of data (addition or removal of rows) it gives desired results the next time query is run.

–> Its 4 gems are:

1. ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax:

ROW_NUMBER() OVER ( [ < partition_by_clause > ] < order_by_clause > )

2. RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Syntax:

RANK() OVER ( [ < partition_by_clause > ] < order_by_clause > )

3. DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Syntax:

DENSE_RANK() OVER( [ ] < order_by_clause > )

4. NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax:

NTILE(integer_expression) OVER( [ ] < order_by_clause > )

–> Now lets take an example, simple one of a class of students, their marks & class:

select 'A' [class], 80 [marks], 'manoj' stuName
into #tempTable
UNION
select 'A', 70 [marks],'harish' stuName
UNION
select 'A', 80 [marks],'kanchan' stuName
UNION
select 'A', 90 [marks],'pooja' stuName
UNION
select 'A', 90 [marks],'saurabh' stuName
UNION
select 'A', 50 [marks],'anita' stuName
UNION
select 'B', 60 [marks],'nitin' stuName
UNION
select 'B', 50 [marks],'kamar' stuName
UNION
select 'B', 80 [marks],'dinesh' stuName
UNION
select 'B', 90 [marks],'paras' stuName
UNION
select 'B', 50 [marks],'lalit' stuName
UNION
select 'B', 70 [marks],'hema' stuName

select * from #tempTable

Now on selection this gives you:
class 	marks   name
A 	50 	anita
A 	70 	harish
A 	80 	kanchan
A 	80 	manoj
A 	90 	pooja
A 	90 	saurabh
B 	50 	kamar
B 	50 	lalit
B 	60 	nitin
B 	70 	hema
B 	80 	dinesh
B 	90 	paras

–> The following query shows you how each function works:

select marks, stuName,
    ROW_NUMBER() over(order by marks desc) as [RowNum],
    RANK() over(order by marks desc) as [Rank],
    DENSE_RANK() over(order by marks desc) as [DenseRank],
    NTILE(3) over(order by marks desc) as [nTile]
from #tempTable

Result:
marks stuName  RowNum  Rank DenseRank  nTile
90    pooja    1       1    1          1
90    saurabh  2       1    1          1
90    paras    3       1    1          1
80    dinesh   4       4    2          1
80    kanchan  5       4    2          2
80    manoj    6       4    2          2
70    harish   7       7    3          2
70    hema     8       7    3          2
60    nitin    9       9    4          3
50    anita    10     10    5          3
50    kamar    11     10    5          3
50    lalit    12     10    5          3

–> Here:
– RowNum column lists unique ID’s of students, like Roll Numbers.
– Rank lists student rank with equal ranks those secured equal marks, thus there is no 2nd or 3rd.
– DenseRank lists student ranks with no gaps, so here 3 students came 1st &2nd and only 2 3rd.
– nTile listed students in different but equal groups, can be thought of as different sections.

–> Now, lets use the PARTITION BY option, its same as group by clause. Lets group/partition the students group by their classes A&B:

select class, marks, stuName,
    ROW_NUMBER() over(partition by class order by marks desc) as [RowNum],
    RANK() over(partition by class order by marks desc) as [Rank],
    DENSE_RANK() over(partition by class order by marks desc) as [DenseRank],
    NTILE(3) over(partition by class order by marks desc) as [nTile]
from #tempTable

Result:
class marks stuName  RowNum  Rank DenseRank  nTile
A     90    pooja    1       1    1          1
A     90    saurabh  2       1    1          1
A     80    kanchan  3       3    2          2
A     80    manoj    4       3    2          2
A     70    harish   5       5    3          3
A     50    anita    6       6    4          3
B     90    paras    1       1    1          1
B     80    dinesh   2       2    2          1
B     70    hema     3       3    3          2
B     60    nitin    4       4    4          2
B     50    kamar    5       5    5          3
B     50    lalit    6       5    5          3

You can clearly see that our query has grouped students in 2 Partitions (classes) and then Ranked them.

In my [next post] check how to use OVER Clause & Partition By option with Aggregate functions like, SUM, AVG, MIN, MAX, etc.

–> Check the same demo on YouTube:

Categories: SQL Server 2005 Tags: DENSE_RANK, NTILE, OVER, PARTITION BY, RANK, RANKING FUNCTIONS, ROW_NUMBER

Table variables are not stored in memory, but in tempdb

July 20, 2010 2 comments

Table-Variables just like Temp-Tables also gets stored in tempdb.
This is a misconception among people and online forums that Table-Variables are memory variables or maintained in memory.

–> To prove this I executed following code:

-- Batch #1
SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

-- Batch #2
CREATE TABLE #tempTab (j INT)
INSERT INTO #tempTab VALUES (1)
SELECT * FROM #tempTab

SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

-- Batch #3
DECLARE @tabVar TABLE (i INT)
INSERT INTO @tabVar VALUES (1)
SELECT * FROM @tabVar

SELECT TABLE_NAME
FROM tempdb.INFORMATION_SCHEMA.TABLES
GO

DROP TABLE #tempTab
GO

Batch #1: Excuting first batch gives me 0 rows, means no tables exists in tempdb system database.

Batch #2: The second batch of stmt gives me 1 row with following table name:

#tempTab_____________________________________________________________000000019D39

Batch #3: and the third batch of stmt gives me 2 rows with following table name:

#tempTab_____________________________________________________________000000019D39
#0C4F413A

This clearly shows that the new Table-Variable is created with a random name #0C4F413A on tempdb.

–> Check the full demo here:

Categories: Misconception, tempdb Tags: Table Variables, Temp Tables, tempdb, Temporary Tables

TRY CATCH – Exception Handling in SQL Server

June 16, 2010 11 comments

Similar to C++, Java and other languages SQL Server also has a mechanism to handle exceptions by using TRY-CATCH construct. The TRY block contains the SQL statements that may raise an error and CATCH block contains the handling mechanism to process the error. When any error is raised in the TRY block the control is immediately transferred to the CATCH block, where the Error is handled.

–> Following rules should be taken care off while using TRY-CATCH constructs:
– A TRY block must be followed immediately by the CATCH block.
– Both TRY & CATCH blocks must be inside a Batch, Stored Procedure or a Trigger.
– Only Errors with severity between 10 & 20 that do not close the database connection are caught & handled by TRY-CATCH constructs.

– As per MS BOL, Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. And Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.

–> let’s check how to use TRY-CATCH block:

USE [tempdb]
GO

--// Create a test Stored Procedure
CREATE PROC testPrc (@val VARCHAR(10))
AS
BEGIN
 SELECT 1/@val AS operation
END
GO

--// Test for Divide by 0 (Divide by zero error encountered.)
BEGIN TRY
 EXEC testPrc '0'
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Test for Datatype conversion (Conversion failed when converting the varchar value 'a' to data type int.)
BEGIN TRY
 EXEC testPrc 'a'
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Test nested TRY-CATCH for &quot;Divide by 0&quot; &amp; &quot;Datatype conversion&quot; errors both.
BEGIN TRY
 EXEC testPrc 'a'
END TRY
BEGIN CATCH
 SELECT 'outer block',
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE

 BEGIN TRY
 SELECT 1/0 AS operation
 END TRY
 BEGIN CATCH
 SELECT 'inner block',
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
 END CATCH

END CATCH
GO

--// Test for violation of PK Constraint (Violation of PRIMARY KEY constraint 'PK__testTable__2C3393D0'. Cannot insert duplicate key in object 'dbo.testTable'.)
BEGIN TRY
 CREATE TABLE testTable (a INT PRIMARY KEY)

 INSERT INTO testTable VALUES(1)
 INSERT INTO testTable VALUES(1)
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

SELECT * FROM testTable -- Contains single record with value 1

--// Test for recreating a table that already exists (There is already an object named 'testTable' in the databASe.)
BEGIN TRY
 CREATE TABLE testTable (a INT PRIMARY KEY)
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Test for inserting NULL value on Primary Key column (Cannot insert the value NULL into column 'a', table 'tempdb.dbo.testTable'; column does not allow nulls. INSERT fails.)
BEGIN TRY
 INSERT INTO testTable VALUES(NULL)
END TRY
BEGIN CATCH
 SELECT
 ERROR_NUMBER() AS ERROR_ID,
 ERROR_MESSAGE() AS ERROR_MSG,
 ERROR_SEVERITY() AS ERROR_SEVERITY,
 ERROR_STATE() AS ERROR_STATE,
 ERROR_PROCEDURE() AS ERROR_PROCEDURE,
 ERROR_LINE() AS ERROR_LINE
END CATCH
GO

--// Final Cleanup
DROP TABLE     testTable
DROP PROC testPrc
GO

MS BOL Links for TRY-CATCH:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms179296%28v=SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms179495.aspx (Error Information)