OT: Sql Server 2005 OVER clause

ajj3085 posted on Thursday, November 01, 2007

Hi,

I've been trying in SSIS to count the number of rows with a particular attribute, to be returned with each row. 

So for this set of data:
LineItem ( DocumentNumber, UnitPrice, ProductId )
"12", 34.3, 1
"12", 10, 3
"12", 0.3, 5
"5", 34.3, 1
"5", 0.3, 5
"1", 34.3, 1
"1", 10, 2
"1", 0.3, 3
"1", 34.3, 4
"1", 10, 5
"1", 0.3, 6

I want
DocumentNumber, UnitPrice, ProductId, NumberOfLines
"12", 34.3, 1, 3
"12", 10, 3, 3
"12", 0.3, 5, 3
"5", 34.3, 1, 2
"5", 0.3, 5, 2
"1", 34.3, 1, 6
"1", 10, 2, 6
"1", 0.3, 3, 6
"1", 34.3, 4, 6
"1", 10, 5, 6
"1", 0.3, 6, 6

I need to do this because that's what the other program to which I am exporting data expects.

In Sql 2005, you can do this select and get the desired result
select DocumentNumber, UnitPrice, ProductId, count(*) over (partition by DocumentNumber) as itemcount
from LineItem

You can also use it over some other functions as well.

Hope that helps others!

Andy