Selecting row after condition row

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

General SQL Server Forums

New to SQL Server Programming

Selecting row after condition row

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-10-11 : 11:39:25

Hi

We have a comment table for our system where users reject products and when they reject a product it puts a comment in the comment table then the user adds their own comment of why the product was rejected. What I want to do is be able to gather the comment right after the product rejected comment but unsure how to. The data will look like this:

Product   Comment 
=======   =========
1         'Some comment'
1         'Some Comment'
1         'Product Rejected'
1         'Reason for rejection'

Now I would like to process the rows and do something like take the first comment that says 'product rejected' because this is a system generated string, and then take the comment from the next row and concatenate them.

I have done a similar thing with a function before but it was much easier because each comment I required had a system generated string before it so I could recognise the comments I wanted easily.

I would also think it would be better to include this system generated comment but we have a lot of old comments that we could not not include without a lot of work.

Surely there must be a way of triggering something to take the next row only, like in a function, when the product rejected comment is found?

This is for a report to show the rejected comments for each product.

Thanks for any advice

G

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-11 : 12:01:25

quote:

Product Comment
======= =========
1 'Some comment'
1 'Some Comment'
1 'Product Rejected'
1 'Reason for rejection'

What is your expected output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-11 : 12:09:25

is there a unique valued column in your table to determine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-10-12 : 04:30:53

quote:
Originally posted by namman

quote:

Product Comment
======= =========
1 'Some comment'
1 'Some Comment'
1 'Product Rejected'
1 'Reason for rejection'

What is your expected output?

I am looking for:

'Product Rejected, Reason for Rejection'

I know how to do this in a function as have done similar thing before, it's just getting this piece of information out.

G

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 04:35:05

You want it comma seperated irrespective of the ProductId?

PBUH

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-10-12 : 04:45:47

quote:
Originally posted by visakh16

is there a unique valued column in your table to determine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There is a PK value for each comment row.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-10-12 : 08:07:51

quote:
Originally posted by Sachin.Nand

You want it comma seperated irrespective of the ProductId?
PBUH

This was not the issue, I was trying to select the comment and the one immediately after it only, then concatenate it. I know how to create a function and concatenate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 03:01:01

quote:
Originally posted by Grifter

quote:
Originally posted by visakh16

is there a unique valued column in your table to determine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There is a PK value for each comment row.

is it a contiguous valued fields like an identity?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources