CODEWITHSUNDEEP
c#sqlsql-servert-sql
Chris
Chris

Reputation: 7621

Returning duplicate row in T-SQL

I'm currently writing an SQL statement which outputs the details of a delivery for a customer, into a nice repeater grid. However, based on a field in the customers table (with an integer determing how many rows), i need to be able to generate that many rows with the exact same details in them.

I.E: A delivery has one row, but based on the number in the customers table for that customer, the same row needs to be outputted that many times in the SQL.

Any ideas? Sorry if it's a bit confusing, and I know it sounds stupid, but from the SQL 'labels' are being generated for that delivery, and multiple labels may be required.

Upvotes: 2

Views: 334

Answers (3)

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

I think what would work well here is a recursive CTE. In my example I make table variables with some sample data that are only useful for demonstration. The part from DECLARE @RepeatedLevel = 3; and onwards is the query itself.

-- Sample table variables are purely for demonstration.
DECLARE @Customer TABLE
(
    ID INT IDENTITY(1,1)
    , Name VARCHAR(25)
)

DECLARE @Delivery TABLE
(
    ID INT IDENTITY(1,1)
    , CustomerID INT
    , DeliveryDate DATE
);

INSERT INTO @Customer
VALUES ('Jeremy'), ('Chris'), ('Sachin'), ('AdaTheDev');

INSERT INTO @Delivery
VALUES (1, '20100602')
    , (2, '20100726')
    , (2, '20110103')
    , (3, '20090401')
    , (3, '20100214')
    , (3, '20100726')
    , (4, '20100713')
    , (4, '20100719')
    , (4, '20100813')
    , (4, '20101028');

DECLARE @RepeatedLevel INT = 3;

WITH DeliverysRepeated AS
(
    SELECT
        Customer.Name
        , Delivery.DeliveryDate
        , 1 AS RecursionLevel
    FROM @Customer Customer
    INNER JOIN @Delivery Delivery
        ON Delivery.CustomerID = Customer.ID

    UNION ALL

    SELECT
        DeliverysRepeated.Name
        , DeliverysRepeated.DeliveryDate
        , DeliverysRepeated.RecursionLevel + 1 AS RecursionLevel
    FROM DeliverysRepeated 
    WHERE DeliverysRepeated.RecursionLevel < @RepeatedLevel
)
SELECT
    DeliverysRepeated.Name
    , DeliverysRepeated.DeliveryDate
FROM DeliverysRepeated
ORDER BY Name, DeliveryDate DESC

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147324

Here's one way, making use of a "Numbers" table which you can just create and populate with numbers from 1 to n (one-off process)

SELECT d.*
FROM Delivery d
    JOIN Customer c ON d.CustomerID = c.ID
    JOIN @Numbers n ON c.NumberField >= n.Num

e.g. basic example

DECLARE @T1 TABLE (ID INTEGER)
DECLARE @T2 TABLE (ID INTEGER, RowCnt INTEGER)
INSERT @T1 VALUES (1)
INSERT @T1 VALUES (2)
INSERT @T1 VALUES (3)
INSERT @T1 VALUES (4)
INSERT @T2 VALUES (1,1)
INSERT @T2 VALUES (2,2)
INSERT @T2 VALUES (3,4)

DECLARE @Numbers TABLE (Num INTEGER)
INSERT @Numbers VALUES(1)
INSERT @Numbers VALUES(2)
INSERT @Numbers VALUES(3)
INSERT @Numbers VALUES(4)
INSERT @Numbers VALUES(5)

SELECT t1.*
FROM @T1 t1
    JOIN @T2 t2 ON t1.ID = t2.ID
    JOIN @Numbers n ON t2.RowCnt >= n.Num

UPDATE
If you don't want to create a physical numbers table, you can (for numbers 1-2048) use spt_values like this:

SELECT t1.*
FROM @T1 t1
    JOIN @T2 t2 ON t1.ID = t2.ID
    JOIN spt_values n ON t2.RowCnt >= n.Num AND n.type='P'

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

Extended answer from AdaTheDev. Using a CTE to create the number table.

with Numbers(Num)
as
(
    select 1 as Num
    union all
    select (Num + 1) as Num
    from Numbers
    where Num < 1000
)
select t1.*
from @T1 t1
    join @T2 t2 on t1.ID = t2.ID
    join Numbers n on t2.RowCnt >= n.Num option(maxrecursion 1000)

Upvotes: 3

Related Questions