August | 2010 | SQL with Manoj

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: