How to insert 1 to count rows in temp table based on value in another table

Question

How to insert 1 to count rows in temp table based on value in another table

jeffrey.davis

Newbie

Points: 6
More actions
October 9, 2010 at 12:39 am

#230216

Hoping someone can help me.
I am trying to create a function that will return a table result that will have ID and COUNT. I am trying to create a temp table in a function that will return something as follows:
ID count
a 1
a 2
a 3
b 1
b 2
c 1
c 2
c 3
c 4
c 5
...
The ID is the ID of a receiving record and I want the count to go from 1 to the Qty Received in the database table. I will then use this to generate a grouping on a crystal report to print out 1 label for each item received.
Thanks to everyone in advance for any help in this matter.

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

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

You showed us your desired output... now how about some sample data to see what we need to do to generate the output?

Please see the first two links in my signature for how to do this in a way that will actually get some of the people here to jump in and help out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

jeffrey.davis (10/9/2010)
Hoping someone can help me.
I am trying to create a function that will return a table result that will have ID and COUNT. I am trying to create a temp table in a function that will return something as follows:
ID count
a 1
a 2
a 3
b 1
b 2
c 1
c 2
c 3
c 4
c 5
...
The ID is the ID of a receiving record and I want the count to go from 1 to the Qty Received in the database table. I will then use this to generate a grouping on a crystal report to print out 1 label for each item received.
Thanks to everyone in advance for any help in this matter.

Why wouldn't you just do a GROUP BY on ID with a COUNT(*)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)