Weird T-SQL Challenge - 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 2000 Forums

Transact-SQL (2000)

Weird T-SQL Challenge

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-02-03 : 17:34:32

Say we have table:

test_id INT
test_qty INT

With two rows:

1 3
2 2

We want in resultset:

1 3
1 3
1 3
2 2
2 2

One row per each qty.

Any ideas how to do without a loop?

Thanks!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 17:40:28

[code]
Declare @t1 table (a int, b int)
Insert into @t1
Select 1, 3 Union All Select 2, 2

Declare @tally table (n int)
Insert Into @tally
Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 6

Select * From @t1

Select
a,
b
-- ,n
From @t1 t
Inner Join @tally tally
on t.b >= tally.n
Order By a, b
[/code]

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-02-03 : 17:51:34

but what if we dont know max value of qty?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-03 : 17:55:34

what do you mean?? Are you talking millions? A lot of people keep tally tables handy (0 to 1000 or sometimes 10000+)

Pick some number that will always be greater than the qty and then store the tally in a permanent table.

I have a function that builds sequences and returns a recordset, so you could look up the max(qty) and then generate the tally table.

That make sense?

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-02-03 : 17:57:22

yea, youre absolutely right.

Thanks Corey, thae helps a lot.

- Nathan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources