Explode a count 1 to 10 --> 1,2,3,4...

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

General SQL Server Forums

New to SQL Server Programming

Explode a count 1 to 10 --> 1,2,3,4...

Author

Topic

cstlaurent
Starting Member

6 Posts

Posted - 2011-05-17 : 19:03:28

Bonjour,

I have a table of element that store the count of a cable.
For an exemple a 25 pairs cable would have Cable xx 1-25 that is store in three main fields.
Element ID / cable / Cable Low / Cable Hight
122334 xx 1 25

I need to explode the information to a view so I can match it with different table that store information if the pair is occupied or not.

Element ID / Cable / Pair
122334 xx 1
122334 xx 2
122334 xx 3

FYI-the cable count is store this was becaus a route is compose of multiple element.

Any procedure or SQL would help.

the other option would manipulate a dataset but I would rather have the info provide in an SQL view if possible

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-17 : 19:38:52

Do you mean something like this?

declare @table TABLE (ElementID int,Cable varchar(10), low int,high int)
INSERT INTO @Table select 122334 ,'xx' ,1 ,25

select ElementID,Cable ,number
FROM
@table t
CROSS JOIN
master..spt_values
where type = 'p' and number between t.low and t.high

Jim

Everyday I learn something that somebody else already knew

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-17 : 19:42:12

Hi,

[This is about the same solution as jimf's post - I was a bit late with the submit button].

I hope this helps - I'm not definitive I've fully understood the question, so this may not be correct. If it isn't and more detail is given, I can come up with a different/altered solution.

If the goal is to, for the stated record, have 25 resultant records [with the pair value incrementing from 1-25 based on the values in Cable Low/Cable High, this should help.

First, let's create a numbers table. This is useful in doing these types of joins where ranges of numbers are needed. You can read more about numbers tables in my blog at [url]http://www.helpwithsql.com[/url].


-- If using SQL 2000, replace @singleNumbers with #singleNumbers for all of these queries
DECLARE @singleNumbers TABLE
(
      num INT
) 
-- Make this a permanent table - it is useful having a numbers table.
CREATE TABLE Numbers 
(
	num INT
)

DECLARE @counter INT
SET @counter = 0
 
While @counter < 10
BEGIN
      INSERT INTO @singleNumbers VALUES(@counter)
      SET @counter = @counter + 1
END
 
insert into Numbers
SELECT 
(
      n1000place.num*1000 +
      n100place.num*100 + 
      n10place.num*10 + 
      n1place.num
) AS 'number'
FROM @singleNumbers n1place 
CROSS JOIN @singleNumbers n10place
CROSS JOIN @singleNumbers n100place
CROSS JOIN @singleNumbers n1000place
ORDER BY number

Now, let's populate some data to work with:


create table Cables(elementid int, cable varchar(10), cableLow int, cableHigh int)
insert into Cables values('122334','xx',1, 25)
insert into Cables values('444444','yy',1, 15)
insert into Cables values('444444','zz',16, 22)

Here's the query to get the result:


select elementid, cable, n.num From Cables c1
inner join Numbers n on n.num >= cableLow and n.num <= cableHigh

I hope this helps. If I misinterpreted the question, please respond.

Visit http://www.helpwithsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources