Multiple rows by number in cell

Question

I have table multiple:

CREATE TABLE multiple
(
    id int,
    Param1 int,
    Param2 int,
    Param3 int,
    Param4 int
);
INSERT INTO multiple VALUES 
    (1, 1, 2, 3, 0),
    (2, 3, 1, 0, 0),
    (3, 1, 2, 2, 1);
SELECT * FROM multiple;

I want to achieve with t-SQL new table like this: (Number of rows = Value in Param attributes)

Counter  ID TYPE
1        1  Param1
2        1  Param2
3        1  Param2
4        1  Param3
5        1  Param3
6        1  Param3
7        2  Param1
8        2  Param1
9        2  Param1
10       2  Param2
11       3  Param1
12       3  Param2
13       3  Param2
14       3  Param3
15       3  Param3
16       3  Param4

I adder here this text because I have error in editor that it looks like my post is mostly code;

Can you please write sample desc output,because images are blocked in our firewall — TheGameiswar, Jan 14 '16 at 11:24
I think what you wants to `Achive` example in wrong .. can you please correct this .. here a clue `total count 1 in achive is 6` while ` .. your inserted 1 count is `5` — Moumit, Jan 14 '16 at 12:22
using http://stackoverflow.com/questions/33623321/convert-rows-into-columns-sql-server/33623941#33623941 .. your problem will get solve — Moumit, Jan 14 '16 at 12:26

score 2 · Answer 1 · answered Jan 14 '16 at 12:55

Hey you can use following code to achieve your goal :

CREATE TABLE multiple
(
    id int,
    Param1 int,
    Param2 int,
    Param3 int,
    Param4 int
);
INSERT INTO multiple VALUES 
    (1, 1, 2, 3, 0),
    (2, 3, 1, 0, 0),
    (3, 1, 2, 2, 1);

SELECT * FROM multiple;

CREATE TABLE multiple1
(
    ID int,
    TYPE VARCHAR(10)
)
DECLARE @id int = 0

WHILE (@id<=(SELECT Count(id) FROM multiple))
BEGIN
DECLARE @Count int = 0
DECLARE @Count1 int = 0
DECLARE @Count2 int = 0
DECLARE @Count3 int = 0

    WHILE(@Count<(SELECT Param1 FROM  multiple where id = @id)) 
    BEGIN
        INSERT INTO multiple1 VALUES ((Select id from multiple where id = @id),'Param1')
        SET @Count = @Count + 1
    END
    WHILE(@Count1<(SELECT Param2 FROM  multiple where id = @id)) 
    BEGIN
        INSERT INTO multiple1 VALUES ((Select id from multiple where id = @id),'Param2')
        SET @Count1 = @Count1 + 1
    END
    WHILE(@Count2<(SELECT Param3 FROM  multiple where id = @id)) 
    BEGIN
        INSERT INTO multiple1 VALUES ((Select id from multiple where id = @id),'Param3')
        SET @Count2 = @Count2 + 1
    END
    WHILE(@Count3<(SELECT Param4 FROM  multiple where id = @id)) 
    BEGIN
        INSERT INTO multiple1 VALUES ((Select id from multiple where id = @id),'Param4')
        SET @Count3 = @Count3 + 1
    END

    SET @id = @id + 1
END

SELECT * FROM multiple1

score 1 · Accepted Answer · answered Jan 14 '16 at 13:36

Have you heard of "tally tables"? They'll make quick work of this. Here is an example solution that uses one. It works by joining on each column where the number is <= the value in the Param attributes, giving you the number of rows you need. Then the id and type are selected.

Note this example uses a tally table that only counts up to 256, so if you need more, you can add an extra level - just be sure to put your tally table into a temp table otherwise it will go from taking a really fast time to execute, to being a really slow time.

CREATE TABLE multiple
(
    id int,
    Param1 int,
    Param2 int,
    Param3 int,
    Param4 int
);
INSERT INTO multiple VALUES 
    (1, 1, 2, 3, 0),
    (2, 3, 1, 0, 0),
    (3, 1, 2, 2, 1);
SELECT * FROM multiple;


-- Tally table known as a "Ben-Gan" style Tally
WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv3)
SELECT
    ROW_NUMBER() OVER (ORDER BY Id) [Counter],
    Id,
    [Type]
FROM
(
    SELECT Id, 'Param1' AS [Type] FROM multiple INNER JOIN Tally ON multiple.Param1 >= Tally.n UNION ALL
    SELECT Id, 'Param2' AS [Type] FROM multiple INNER JOIN Tally ON multiple.Param2 >= Tally.n UNION ALL
    SELECT Id, 'Param3' AS [Type] FROM multiple INNER JOIN Tally ON multiple.Param3 >= Tally.n UNION ALL
    SELECT Id, 'Param4' AS [Type] FROM multiple INNER JOIN Tally ON multiple.Param4 >= Tally.n
) [single]

Multiple rows by number in cell

2 Answers2