Query - 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

SQL Server Development (2000)

Query

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 02:11:11

Hi,

I have one table: say
Col1 Col2
A 2
B 1
C 3
...
now i wants o/p like this

Col1 Col2
A 1
A 1
B 1
C 1
C 1
C 1
...

thanks in advance,

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-14 : 02:28:35

[code]
declare @t table
(
col1 varchar(10),
col2 int
)

insert @t
select 'A', 2 union all
select 'B', 1 union all
select 'C', 3

select t1.col1, 1
from @t t1 join master..spt_values t2
on t1.col2 >= t2.number and t2.[name] is null and t2.number > 0[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 02:47:00

thanks Harsh,

can u explain me what the master..spt_values is? Is it DB object or something else, as i m not aware with this.

thanks again,

Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 02:50:17

quote:
Originally posted by mahesh_bote

thanks Harsh,

can u explain me what the master..spt_values is? Is it DB object or something else, as i m not aware with this.

thanks again,

Mahesh

I got it, Its user table in Master DB. But why u use this table in join, what is the purpose?

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-14 : 02:57:53

Because to implement your solution, I need table of numbers and I am lazy enough not to create a new one when MS is kind enough to provide ready-made one (although it's for the internal purpose).

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-14 : 04:17:37

or try this...

Create Table Tab
(
col1 varchar(10),
col2 int
)

insert into Tab
select 'A', 2 union all
select 'B', 1 union all
select 'C', 3

Select Tab.Col1, 1 As Cnt
From Tab,
(Select (Select Count(*) From Tab Where Tab.Col1 <= Tab2.Col1) As SrNo
,Col1
,Col2
From Tab As Tab2
) Tmp
Where
Tab.Col2 >= Tmp.SrNo
Order By Tab.Col1

Note:
(but problem with this qry is that, there must be squencial nos in col2. e.g. if u have 10 as max val in col2, there must be 1 to 10 nos in col2.)

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-14 : 04:40:57

Besides that, its way too costly and hard to interpret. Just check the execution plan.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 10:47:46

quote:
Originally posted by mahesh_bote

Hi,

I have one table: say
Col1 Col2
A 2
B 1
C 3
...
now i wants o/p like this

Col1 Col2
A 1
A 1
B 1
C 1
C 1
C 1
...

thanks in advance,

Mahesh

May I know why you need this?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-14 : 11:03:56

May be just for fun? to know whether it is possible to do this with set-based solution...just a brain-teaser!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources