Mutiple Row Creation 1

[GShen]

What I am trying to do is create multiple rows from 1 record.. easily.

This is for a report. I can write repitive cursors but I just wondering if there is an easier way. For ex.
Say my select returns a set of records each with a field to tell me how many times I need to repeat that record. (simple example)
5 Tuesday
1 Wednesday
2 Sunday

I want the final select result to be for printing.
Tuesday
Tuesday
Tuesday
Tuesday
Tuesday
Wednesday
Sunday
Sunday
-----------------------------------------------------------------

Now, utopia would be to be able to do this in the 1st select.

Say I had a 3 entries in the table prior to the select with
count Data
5 Tuesday
1 Wednesday
2 Sunday

Could I dynamically (without a cursor), generate the 8 records?

I tried
1) SELECT TOP (select count from xxx) *
from Table.
2) Inner joining on the count of records.
3) Having clause with a select

And about 900 other gyrations to no avail. My actual SELECT gets the orig. data from mult. tables with 3 union queries. It is quite complex, as one of the fields will be a counter of the number of records. I am just trying to simplify it for this post.

The only option I see now is to run the orig. select with its unions and capture the counter in a temp file. (something like I have as the example)
Then writing a cursor and looping thru each record by the counter number of times and adding the records to the temp file.
Then selecting the temp file for the report. I am cringing to think how long this will take.

If anyone has ideas or needs more info., I am all ears.

Remember when... everything worked and there was a reason for it?

 

[IanWaterman]

why not create a table with entries

1
2
2
3
3
3
4
4
4
4
etc and then join your query to that via your count field.

Ian

 

[SQLScholar]

You can do this with a recursive CTE. Please see below:

CREATE TABLE #List
    (
      Number INT ,
      Daysname VARCHAR(20)
    )

INSERT  INTO #List
        ( Number, Daysname )
VALUES  ( 5, 'Tuesday' )
INSERT  INTO #List
        ( Number, Daysname )
VALUES  ( 1, 'Wednesday' )
INSERT  INTO #List
        ( Number, Daysname )
VALUES  ( 2, 'Sunday' )


GO
WITH    Biglist ( Daysname, number )
          AS (
-- Anchor member definition
               SELECT   daysname ,
                        Number
               FROM     #List
               UNION ALL
-- Recursive member definition
               SELECT   Biglist.daysname ,
                        Biglist.Number - 1 AS number
               FROM     #List AS e
                        INNER JOIN Biglist ON e.Daysname = Biglist.Daysname
                                              AND Biglist.Number - 1 > 0
             )
    SELECT  Daysname
    FROM    Biglist
    ORDER BY Daysname

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

I would suggest that you use a numbers table to do this. There are a million and 1 uses for a numbers table, this being one of them.

In my database, I have a numbers table With 1 column (Num) and 100,000 rows. It takes less than a megabyte to store this numbers tables.

Assuming a numbers table, your query would be....

Select  YourColumnName
From    YourTable T
        Inner Join Numbers
          On T.YourNumberColumn >= Numbers.Num

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[GShen]

Hey everyone,

I tried a few things.

IanWaterman - This seems to what I want and keeps it simple. My table would only need to go to 10. The user said 5 is the most he would ever need multiples for so of course I doubled it to 10.

SQlScholar- saving this for a rainy day. It may do what I want but I will never be able to explain this anyone and I will really want to keep it simple. It seems a tad complex. Like I said, my query now has 15 tables in it already, it is not as simple as my example. Adding the multi. number table per IanWaterman has 0 degragation on the query.

gmmastros - This is similar to IanWaterman. I tried it but I am not sure what it would do. It is only giving me 1 occurance. I need multi. rows. I have a table out on my system already with numbers 1-10,000 for something else that I do but like I said, I am not sure what this would do for me.

If I missed something let me know. Otherewise, so far, IanWatermans logic is going to be used.

Thanks everyone!!!!!




Remember when... everything worked and there was a reason for it?

 

[gmmastros]

GShen,

Can you show the query you are using?

I know my advice is similar to Ian's, the real trick is that you are joining on a >= condition instead of the normal = condition.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IanWaterman]

My vote is with George, so much neater. I like the lateral thinking of using a >= join. Brilliant!

Ian

 

[GShen]

Hi All,
George, I must have had brain fry yesterday. That plus,I did not have the field I needed in the table available yet. I just added it. I ran Ian's code and it worked perfectly.
I then did a double take on your code because I could not see why it would not work. I reran it, perfecto. I do however, do a left join vs. the inner join because I want all the other records to populate once. Works like a charm.

Thanks so much. I like this better because I already have a table in my system that I can use.

Appreciate the help everyone,

Gary

Remember when... everything worked and there was a reason for it?