CODEWITHSUNDEEP
sqlsql-servert-sqlview
Daniel de Jong
Daniel de Jong

Reputation: 13

TSQL: How to split a row in multiple rows based on the number in a cell

I want to create a view that splits a row based on the number in the cell "QTY"

Row 1 has value 1 in cell "QTY" and should result in 1 row
Row 2 has value 2 in cell "QTY" and should result in 2 rows
Row 3 has value 4 in cell "QTY" and should result in 4 rows

My table 

QTY  ITEM
1  | aaa
2  | bbb
4  | ccc

My result should look like this

QTY  ITEM
1  | aaa
2  | bbb
2  | bbb
4  | ccc
4  | ccc
4  | ccc
4  | ccc

Create table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mytable](
    [QTY] [int] NULL,
    [Item] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[mytable] ([QTY], [Item]) VALUES (1, N'aaa       ')
GO
INSERT [dbo].[mytable] ([QTY], [Item]) VALUES (2, N'bbb       ')
GO
INSERT [dbo].[mytable] ([QTY], [Item]) VALUES (4, N'ccc       ')
GO

Upvotes: 1

Views: 371

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try this solution also using the master.dbo.spt_values.

create table MyTable (QTY int, ITEM varchar(10))
insert into MyTable Values
(1, 'aaa'),
(2, 'bbb'),
(4, 'ccc')

SELECT  t1.*, t2.number + 1 RepeatNumber
FROM    MyTable t1
JOIN    master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.QTY

Here is the demo at: db<>fiddle

enter image description here

Upvotes: 0

Thom A
Thom A

Reputation: 96028

I would use a Tally to do this, and then JOIN on No being greater or equal to the value in the Tally:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP(SELECT MAX([No]) FROM dbo.mytable) --if 185 isn't to change, then I would replace the sub query with 185
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --216 rows, add more Ns here, or more NULLs above to make larger
SELECT mt.[No] AS QTY,
       mt.Item       
FROM dbo.mytable mt
     JOIN Tally T ON mt.[No] >= T.I;

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

You could do as:

SELECT T.*
FROM
(
    VALUES
    (1, 'aaa'),
    (2, 'bbb'),
    (4, 'ccc')
) T(Qty, Item) CROSS APPLY
(
    SELECT Number
    FROM Master..SPT_VALUES
    WHERE TYPE = 'P' AND Number > 0 AND Number <= T.Qty
) TT(N)

Note: You can replace master..spt_values with a tally table.

Upvotes: 1

Related Questions