Regarding sql 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

Transact-SQL (2000)

Regarding sql query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-09-07 : 09:57:35

Muthu writes "Hi,
I need to get the duplicate rows from the table depending upon the value of another field in the table.
for ex. I have the table with 3 fields namely,
Prod_Id,Prod_Name,Qty.
Let the table have the values like this,
Prod_Id-Prod_Name-Qty
----------------------
P1 aaa 1
P2 bbb 2
P3 ccc 3
P4 ddd 1
What I want is,
the result should be like this,
Prod_Id-Prod_Name-Qty
----------------------
P1 aaa 1
P2 bbb 2
P2 bbb 2
P3 ccc 3
P3 ccc 3
P3 ccc 3
P4 ddd 1

that means ,depending upon the qty value ,I want to get the number of records.

Any one can help me,Please!!!
Thanks & Regards,
Muthukumar.C"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-07 : 12:01:54

Here is one way:


set nocount on
--------------------------------------------------------
--Create a table of numbers
if object_id('tempdb..#numbers') > 0
	drop table #numbers
create table #numbers (n int primary key clustered)
insert #numbers (n)
select convert(int, n1+n2+n3+n4+n5) as n
from		(select 0 n1 union select 1) n1
cross join	(select 0 n2 union select 2) n2
cross join	(select 0 n3 union select 4) n3
cross join	(select 0 n4 union select 8) n4
cross join	(select 0 n5 union select 16) n5
--------------------------------------------------------
--Here is your sample table
if object_id('tempdb..#yourTable') > 0
	drop table #yourtable
create table #yourTable
	(Prod_Id varchar(2)
	,Prod_Name varchar(3)
	,Qty int)
insert #yourTable
select 'P1', 'aaa', 1 union all
select 'P2', 'bbb', 2 union all
select 'P3', 'ccc', 3 union all
select 'P4', 'ddd', 1
--------------------------------------------------------

select	prod_id, prod_name, qty
from	#yourTable yt
cross join #numbers
where	n > 0
and	qty >= n

output:
prod_id prod_name qty         
------- --------- ----------- 
P1      aaa       1
P2      bbb       2
P2      bbb       2
P3      ccc       3
P3      ccc       3
P3      ccc       3
P4      ddd       1

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-07 : 12:08:46

Here's a stored procedure you could create to do it. It assumes that your table is named Prod.

CREATE PROC GetProds
AS
CREATE TABLE #ProdIds
(Prod_Id char(2))
INSERT #ProdIds
SELECT Prod_Id FROM Prod
DECLARE @Qty int
DECLARE @ProdId char(2)
SET @ProdId = ''
SELECT @ProdId = min(#ProdIds.Prod_Id)
FROM #ProdIds
WHERE #ProdIds.Prod_Id > @ProdId
WHILE @ProdId IS NOT NULL
BEGIN
SELECT @Qty = Qty FROM Prod WHERE Prod_Id = @ProdId
WHILE @Qty > 1
BEGIN
INSERT #ProdIds VALUES (@ProdId)
SET @Qty = @Qty - 1
END
SELECT @ProdId = min(#ProdIds.Prod_Id)
FROM #ProdIds
WHERE #ProdIds.Prod_Id > @ProdId
END
SELECT Prod.*
FROM Prod
INNER JOIN #ProdIds on Prod.Prod_Id = #ProdIds.Prod_Id
ORDER BY Prod.Prod_Id

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-09-07 : 17:51:13

In case you are using SQL 2005, you can use the following statement to create the tmp table #numbers mentioned below in TG's answer. This is just another way to create a result set of seq numbers with your own limit.

With numbers(n) as
(
select 1 as n
union all
select n+ 1 from numbers
where n < 1000
)

select * from numbers OPTION (MAXRECURSION 999);

dvsant
Starting Member

1 Post

Posted - 2006-09-08 : 06:08:50

Hi,
Here is one solution to your problem

create table prod (Prod_Id char(2), Prod_Name char(3), Qty int)
insert into prod values ('P1','aaa',1)
insert into prod values ('P2','bbb',2)
insert into prod values ('P3','ccc',3)
insert into prod values ('P4','ddd',1)
insert into prod values ('P5','eee',5)

create table #tmp (qty int)
declare @i int
set @i = 1
while @i <= (select max(qty) from prod)
begin
insert into #tmp values (@i)
set @i = @i + 1
end

Select Prod_Id, Prod_Name, Qty from
(select distinct p1.*, p2.qty as qt
from prod p1
cross join #tmp p2
where p1.qty > p2.qty
union all
select *, qty as qt from prod) Q
order by 1,2,3

Regards,
Deepak Sant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources