What are the alternative to cursors?

Question

What are the alternative to cursors?

ssrs

SSC Rookie

Points: 45
More actions
February 19, 2009 at 12:59 pm

#94575

I need to normalize this data making the member column the key:
MEMBER CLAIM #DateFrom DateTo Rank
1046241144232804/01/08 04/03/08 1
1046241157864209/01/08 09/04/082
1046241157864309/04/08 09/05/083
1047239138581702/01/08 02/04/081
1048928147720704/01/08 04/03/081
1048928170501904/01/08 04/03/082
I want to end up with some like this:
MEMBER CLAIM #DateFrom DateToCLAIM # DateFrom_2 DateTo_2....
I will have as many columns as the customer has claims.
It will have a # of columns = # of claims having the greater amount of claims.
I do not want to create a cursor because is a big amount of data
to loop through.
I was wondering if there is any other way around it.
Thanks,
All comments are appreciate it 🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

ang_mora (2/19/2009)
I need to normalize this data making the member column the key:
MEMBER CLAIM #DateFrom DateTo Rank
1046241144232804/01/08 04/03/08 1
1046241157864209/01/08 09/04/082
1046241157864309/04/08 09/05/083
1047239138581702/01/08 02/04/081
1048928147720704/01/08 04/03/081
1048928170501904/01/08 04/03/082
I want to end up with some like this:
MEMBER CLAIM #DateFrom DateToCLAIM # DateFrom_2 DateTo_2....
I will have as many columns as the customer has claims.
It will have a # of columns = # of claims having the greater amount of claims.
I do not want to create a cursor because is a big amount of data
to loop through.
I was wondering if there is any other way around it.
Thanks,
All comments are appreciate it 🙂

If you want to normalize this, then you'd create a child table for Claims, where each row in the Claims table is a unique claim. The way you are thinking right now, you'd be adding new columns each time a member had a new claim.

Does this short thought help you at all?

ssrs SSC Rookie Points: 45 More actions · Answer 2

Thanks Lynn for the prompt response.

I actually misused the word normalize I just thought it would be easier to understand but what I'm actually doing is writing a report out of that result set.

I must access all claim info for every member. I do not have any variables or parameters so I though it would be easier to make a single row per customer to access it easier.

does it make sense?

a

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

As this now appears to be a report, you may want to read the articles referenced below in my signature block that talk about Cross Tabs and Pivots. Those should be a good starting point for what you are trying to accomplish.

ssrs SSC Rookie Points: 45 More actions · Answer 4

thanks a lot!!! 🙂

The Cross tabs are just what I was looking for! 😀