SELECT multiple rows from a single 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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT multiple rows from a single row

Author

Topic

notmyrealname

98 Posts

Posted - 2010-08-31 : 10:11:24

Hi,

Does anyone know if it is possible to (using a SELECT query) to return multiple rows for rows with a Quantity > 1?

For example...

Part: Qty
a1: 3
a2: 1
a3: 6

...would return...

Part
a1
a1
a1
a2
a3
a3
a3
a3
a3
a3

I would like to return individual rows for the total Qty of each part. I would love to do this in a single query but i am doubtful that it is possible.

Any ideas?

Thanks,
Joel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-31 : 10:26:38

select part from your_table as t1
cross join master..spt_values as t2
where qty<=t2.number and type='0'

Madhivanan

Failing to plan is Planning to fail

notmyrealname

98 Posts

Posted - 2010-08-31 : 11:42:49

Hey Madhivanan,

You rock! I don't care what everyone else says about you.

I like how you teach a man to fish. I scratched my head for a bit looking at your solution but after a little reseach on the spt_values table i finally figured out what you were doing. Makes perfect sense now.

I did tweak it a bit by adding my own NumberList table to join against because i do have Qty values into the thousands.

Thanks a ton for the tip.

Joel

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-01 : 05:06:11

quote:
Originally posted by notmyrealname

Hey Madhivanan,

You rock! I don't care what everyone else says about you.

I like how you teach a man to fish. I scratched my head for a bit looking at your solution but after a little reseach on the spt_values table i finally figured out what you were doing. Makes perfect sense now.

I did tweak it a bit by adding my own NumberList table to join against because i do have Qty values into the thousands.

Thanks a ton for the tip.

Joel

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources