recursive query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

recursive query help

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-08-07 : 11:05:30

hi,
Can anyone help me count the number of posts a person has made in each message. ?
I have a table that looks like :

userid pid cid
2 12 14
2 14 0
2 56 57
2 57 101
2 101 0
5 5 0
7 122 140
7 140 0

How can I count the rows in such a way that my results are like :
userid [count] originalid
2 2 12
2 3 56
5 1 101
7 2 122

Thank you for any help.
jamie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 11:15:39

Have a look at recursive CTE on books online

http://msdn.microsoft.com/en-us/library/ms186243.aspx

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-08-07 : 11:29:32

ok, thank you, this example is perfect.

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-08-07 : 12:01:11

hi, this example isn't really what I require.
this basically would put my data in a similar format as my original table.
What I really need is the second stage , ie, how to group and count those records..
How can I count the rows in such a way that my results are like :
userid [count] originalid
2 2 12
2 3 56
5 1 101
7 2 122

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-08-08 : 06:48:16

hi,
still struggling with this query.
am using the recursive example to put my data in the correct format.
however I need to beable to show the originalid in each row to do the grouping, does anyone know how to do that ?

so consider :
userid pid cid
2 12 14
2 14 0

needs to be :
userid pid cid original
2 12 14 12
2 14 0 12

is this possible ?

thank you for any help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources