Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that contains records in an un-expanded form. Each record has an associated integer weight that essentially informs us how many times the record should be replicated in order to get the true population.

Say, I have 3 records in my table. sampn ids a unique record and weight is the frequency weight. The un-expanded dataset looks like this:

sampn   weight  attrib1 attrib2 attrib3
    1       2       23      32      65
    2       1       32      56      75
    3       3       54      25      87

Once expanded, the dataset will be like this (note - I removed the weight field - but this is not essential):

sampn   attrib1 attrib2 attrib3
    1       23      32      65
    1       23      32      65
    2       32      56      75
    3       54      25      87
    3       54      25      87
    3       54      25      87

I have tried to do this using cursors but it is taking a really long time to execute. Is there a clever way to do this really fast? Any predefined T-SQL stored procedure that achieves this?

Update
All, Thanks for the answers! Really great learning experience! Performed the expansion operation on my dataset. Paul's auxiliary table of numbers had the best execution time.

share|improve this question
    
Do you actually care about having a result set like that, or are you expanding it in order to do something else, like compute an aggregate of the data? –  Jon Seigel Jul 25 '13 at 2:42
    
The purpose is to create the population set for use in micro-simulation. Once I get the table in this form I will start assigning unique ids for each record, perform some additional operations before feeding it into the micro-simulation framework. –  RazorXsr Jul 25 '13 at 3:58

3 Answers 3

up vote 8 down vote accepted

An efficient way to perform this task uses an auxiliary table of numbers. This is simply a table with integers from 1 to n, where 'n' is perhaps a million or so. A numbers table comes in handy for all sorts of regular tasks.

CREATE TABLE dbo.Demo
(
    SampleNumber    integer IDENTITY NOT NULL,
    SampleWeight    integer NOT NULL,
    Attribute1      integer NOT NULL,
    Attribute2      integer NOT NULL,
    Attribute3      integer NOT NULL,

    CONSTRAINT [PK dbo.Demo SampleNumber]
        PRIMARY KEY (SampleNumber),

    CONSTRAINT [CK dbo.Demo SampleWeight 1-50]
        CHECK (SampleWeight BETWEEN 1 AND 50)
);

INSERT INTO dbo.Demo
    (
    SampleWeight,
    Attribute1, 
    Attribute2, 
    Attribute3
    )
VALUES
    (2, 23, 32, 65),
    (1, 32, 56, 75),
    (3, 54, 25, 87);

SELECT
    D.SampleNumber,
    D.Attribute1,
    D.Attribute2,
    D.Attribute3
FROM dbo.Demo AS D
JOIN dbo.Numbers AS N
    ON N.n BETWEEN 1 AND D.SampleWeight;

DROP TABLE dbo.Demo;

Execution plan:

Plan

Output:

╔══════════════╦════════════╦════════════╦════════════╗
║ SampleNumber ║ Attribute1 ║ Attribute2 ║ Attribute3 ║
╠══════════════╬════════════╬════════════╬════════════╣
║            1 ║         23 ║         32 ║         65 ║
║            1 ║         23 ║         32 ║         65 ║
║            2 ║         32 ║         56 ║         75 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
║            3 ║         54 ║         25 ║         87 ║
╚══════════════╩════════════╩════════════╩════════════╝

SQLfiddle here.

share|improve this answer
2  
Good stuff and +1 for number table. I had used it in past, but this time did not think of using it. –  Kin Jul 25 '13 at 13:22
    
@Paul: For this exercise, I would need the max number in the Number table to be the max SampleWeight? –  RazorXsr Jul 25 '13 at 17:28
2  
Right. I would recommend that you make your number table larger though because I am sure you will find another use for it :) –  Adam Haines Jul 25 '13 at 20:28
1  
@RazorXsr See Adam's comment above. There is no disadvantage to using a Number table with more rows than you'll need for this query, and they do come in very handy. A million is a good number; I included a creation script in the SQLfiddle. –  Paul White Jul 26 '13 at 10:05

You can use CTE to do it.

here is a sql fiddle for it : http://sqlfiddle.com/#!3/0b172/8

create table t(sampn int, weight   int, attrib1 int, attrib2 int, attrib3 int);

insert into t values    (1 ,      2 ,      23 ,     32   ,   65)
 insert into t values   (2  ,     1  ,     32  ,    56    ,  75)
  insert into t values  (3   ,    3   ,    54   ,   25     , 87)

-- solution using cte.

;WITH cte (
    sampn
    ,[weight]
    ,attrib1
    ,attrib2
    ,attrib3
    ,RepeatedTime
    )
AS (
    SELECT sampn
        ,[weight]
        ,attrib1
        ,attrib2
        ,attrib3
        ,1
    FROM t
    WHERE [weight] <> 0 -- this will take care if the weight is ZERO

    UNION ALL

    SELECT sampn
        ,[weight]
        ,attrib1
        ,attrib2
        ,attrib3
        ,RepeatedTime + 1
    FROM cte a
    WHERE a.[weight] > a.RepeatedTime
    )
SELECT sampn
    ,attrib1
    ,attrib2
    ,attrib3
FROM cte  
ORDER BY sampn
OPTION (MAXRECURSION 100) -- change this if you have more recursions
share|improve this answer
    
Here the MAXRECURSION() is equivalent to looping as many times as weight? Can you please point me to a good resource that explains how CTE works? –  RazorXsr Jul 25 '13 at 17:38
    
@RazorXsr check the link that is embedded in CTE in my answer. I think performance wise, Paul's answer is what you want. I did not think about number table when answering. –  Kin Jul 25 '13 at 18:28

I agree with Paul that the numbers table is probably the best solution that you are going to come up with in terms of manageability and performance. With that said, you could approach this problem from a different angle, using XML.

This solution is fairly easy to understand. Performance on a small number of rows should be fine, but for a large number of rows, you will have to test.

Update: I have added the exact code that I am using that is working, in my test environment.

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.Demo') IS NOT NULL
BEGIN
    DROP TABLE dbo.Demo;
END
GO

CREATE TABLE dbo.Demo
(
    SampleNumber    integer IDENTITY NOT NULL,
    SampleWeight    integer NOT NULL,
    Attribute1      integer NOT NULL,
    Attribute2      integer NOT NULL,
    Attribute3      integer NOT NULL,

    CONSTRAINT [PK dbo.Demo SampleNumber]
        PRIMARY KEY (SampleNumber),

    CONSTRAINT [CK dbo.Demo SampleWeight 1-50]
        CHECK (SampleWeight BETWEEN 1 AND 50)
);

INSERT INTO dbo.Demo
    (
    SampleWeight,
    Attribute1, 
    Attribute2, 
    Attribute3
    )
VALUES
    (2, 23, 32, 65),
    (1, 32, 56, 75),
    (3, 54, 25, 87);
GO

SELECT 
    y.SampleNumber,
    Att.vals.value('@Attribute1','int'),
    Att.vals.value('@Attribute2','int'),
    Att.vals.value('@Attribute3','int')
FROM(
    SELECT 
        SampleNumber,
        Attribute=(SELECT CAST(REPLICATE(CAST(Attribute.Data AS VARCHAR(MAX)),SampleWeight) AS XML))
    FROM dbo.Demo
    CROSS APPLY(   
        SELECT 
            [@Attribute1] = Attribute1,
            [@Attribute2] = Attribute2,
            [@Attribute3] = Attribute3
        FOR XML PATH('data'),ROOT('root'), TYPE
    ) AS Attribute (Data)
) AS y
CROSS APPLY y.Attribute.nodes('/root/data') as Att(vals)
share|improve this answer
    
An interesting way to go about solving the problem. Will look into it and report the performance stats. –  RazorXsr Jul 25 '13 at 14:59
    
The XML approach does not seem to be working - I am getting a blank table. Should I have something setup before I run your code sample apart from dbo.Demo? –  RazorXsr Jul 25 '13 at 17:40
    
Are you using the sample data provided by Paul or your table? It should like the xml nodes method is not returning any rows. are your XML elements the same as the ones in the above code "/root/data". Keep in mind that XML IS case sensitive, so it has to match. –  Adam Haines Jul 25 '13 at 18:49
    
I am using Paul's table... I appended your code below Paul's table definition for Demo. That should work right? –  RazorXsr Jul 25 '13 at 18:55
1  
Right, that should most definitely work. In fact that is what I did to test and come up with the solution. Maybe try to copy the code down again? Perhaps there is something that didn't copy right the first time? –  Adam Haines Jul 25 '13 at 18:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.