CODEWITHSUNDEEP
sql-server
user3851739
user3851739

Reputation: 35

sql query to display the duplicates as per the requested output

I have a sample input table as show below

╔═══════════╗
║ name   id ║
╠═══════════╣
║ anil    3 ║
║ ashok   2 ║
╚═══════════╝

I want to get output for this table as 

╔════════════╗
║ name    id ║
╠════════════╣
║ anil     3 ║
║ anil     3 ║
║ anil     3 ║
║ ashok    2 ║
║ ashok    2 ║
╚════════════╝

how to achieve this using sql query?

Upvotes: 0

Views: 40

Answers (2)

Gidil
Gidil

Reputation: 4137

It's tempting to go for a recursive query but this can be done simply with a tally table.

Something like this:

SET nocount ON; 

IF Object_id('dbo.Tally') IS NOT NULL 
  DROP TABLE dbo.tally 



-- Define how many rows you want in Tally table.  
-- I am inserting only 100 rows 
SET ROWCOUNT 100 

SELECT IDENTITY(int, 1, 1) ID 
INTO   dbo.tally 
FROM   master.sys.all_columns c 
       CROSS JOIN master.sys.all_columns c1 

-- you may use one more cross join if tally table required hundreds of million rows 
SET ROWCOUNT 0 

-- ADD (unique) clustered index 
CREATE UNIQUE CLUSTERED INDEX pkc_tally 
  ON dbo.tally (id) 


SELECT T2.* 
FROM   dbo.tally T1 
       CROSS JOIN table1 T2 
WHERE  T1.id <= T2.id

You can take a look and play around with an example on SQL Fiddle

Upvotes: 0

Matt
Matt

Reputation: 1155

This seems like a job for a Recursive CTE:

create table #input (name varchar(10), id int)
insert into #input values ('anil',3),('ashok',2)

;with cte as
(   select a.name, a.id, a.id as countdown
    from #input a
    union all
    select a.name, a.id, a.countdown-1
    from cte a
    where a.countdown-1 > 0
)
select name,id,countdown from cte
order by 1,2,3 desc

Output

name   id  countdown
====================
anil    3      3
anil    3      2
anil    3      1
ashok   2      2
ashok   2      1

Upvotes: 1

Related Questions