How to select multi record depending on some column's condition?

Question

Say there is a SQL Server table which contain 2 columns: ID, Value

The sample data looks like this:

ID           value
------------------    
1            30
1            30
2            50
2            50
3            50

When I run this query:

 select ID, NEWID(), value 
 from table1 
 order by ID

The result looks like this:

  1            30            E152AD19-9920-4567-87FF-C4822FD9E485
  1            30            54F28C58-ABA9-4DFB-9A80-CE9C4C390CBB
  2            50            ........
  2            50            ........
  3            50            4E5A9E26-FEEC-4CC7-9AC5-96747053B6B2

But what I want is : how many record of ID depending on (sum of value /30 )'s result, for example of ID 2, it's value's sum is 50+50=100, and 100/30=3, so ID 2 will display in query result three times

The final result i want is like this:

  1        E152AD19-9920-4567-87FF-C4822FD9E485
  1        54F28C58-ABA9-4DFB-9A80-CE9C4C390CBB
  2        4E5A9E26-FEEC-4CC7-9AC5-96747053B6B2
  2        ....
  2        ....
  3        D861563E-E01A-4198-9E92-7BEB4678E5D1

Please note ID of 2 display three times, wait for your helps, thanks.

Adriaan Stander · Accepted Answer · 2013-08-08 04:09:19Z

1

How about something like

CREATE TABLE Table1
    ([ID] int, [value] int)
;

INSERT INTO Table1
    ([ID], [value])
VALUES
    (1, 30),
    (1, 30),
    (2, 50),
    (2, 50),
    (3, 50)
;

;WITH SummedVals AS (
        SELECT  ID,
                SUM(value) / 30 Cnt
        FROM    Table1
        GROUP BY ID
)
, Vals AS (
        SELECT  ID,
                Cnt - 1 Cnt
        FROM    SummedVals
        UNION ALL
        SELECT  ID,
                Cnt - 1 Cnt
        FROM    Vals
        WHERE   Cnt > 0
)
SELECT  ID,
        NEWID()
FROM    Vals
ORDER BY 1

SQL Fiddle DEMO

answered Aug 8, 2013 at 4:09

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yangl Over a year ago

thank you for your help,your result is very correct,maybe i need time to understand it.

Adriaan Stander Over a year ago

Try reading up about recursive common table expressions (CTE). That should help with the explanation.

Collectives™ on Stack Overflow

How to select multi record depending on some column's condition?

1 Answer 1

SQL Fiddle DEMO

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related