Writing 2 column data to 1 column

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 2008 Forums

Transact-SQL (2008)

Writing 2 column data to 1 column

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2013-09-18 : 16:57:01

I have a table that contains:

item | quantity
123 | 1
456 | 10
789 | 4

I need to get this data listed into a table that just has item and for it to look like this:

123
456
456
456
456
456
456
456
456
456
456
789
789
789
789

I understand and have accounted for the obvious primary key issue. I just can't figure out how to get the data to display out like this.

Any thoughts?

Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 17:09:24

[code]SELECT
item
FROM
YourTable a
CROSS JOIN master.dbo.spt_values b
WHERE
b.type = 'P' AND b.number < a.quantity; [/code]
If you have more than 2048 quantity in any row, and when you want to use it in production, use a numbers table rather than spt_values table.

benking9987
Posting Yak Master

124 Posts

Posted - 2013-09-18 : 17:26:03

Flawless! Thank you James K.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 17:45:28

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources