Split Row into Multiple Rows

Question

Post reply

Split Row into Multiple Rows

kate-395233

Say Hey Kid

Points: 694
More actions
May 3, 2012 at 3:07 am

#273566

Hi,
I'm trying to split a row of data based around the quantity in the row. For example, if there is quantity 3, I want to split into three rows, each with the same descriptors for the row (in this case, the order number and the product id), but with the qty as 1.
Here is some example code. The first result set shows what output I get. The second result set shows the output I'd like to get.
DECLARE @SPLITROW TABLE
(ORDER_NUMBER INT
, PRODUCT_ID INT
, QTY INT
, VALUE MONEY
)
INSERT INTO @SPLITROW
VALUES (2345, 34567, 3, 15.00)
SELECT ORDER_NUMBER, PRODUCT_ID, QTY, VALUE
FROM @SPLITROW
SELECT ORDER_NUMBER, PRODUCT_ID, 1 AS QTY, 5.00 AS VALUE
FROM @SPLITROW
UNION ALL
SELECT ORDER_NUMBER, PRODUCT_ID, 1 AS QTY, 5.00 AS VALUE
FROM @SPLITROW
UNION ALL
SELECT ORDER_NUMBER, PRODUCT_ID, 1 AS QTY, 5.00 AS VALUE
FROM @SPLITROW
Is it even possible in SQL?
Thank you.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Join to a numbers/tally table

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE

FROM @SPLITROW s

INNER JOIN master.dbo.spt_values t ON t.type='P'

AND t.number BETWEEN 1 AND s.QTY

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kate-395233 Say Hey Kid Points: 694 More actions · Answer 2

Thank you very much!

This is how I modified it (I don't have access to the master db) - and added more rows

DECLARE @count INT

SET @count = 1

DECLARE @TALLY TABLE

(ID INT

, PRIMARY KEY (ID)

)

WHILE (@count < 100440)

BEGIN

INSERT INTO @TALLY

SELECT @COUNT

SET @count = (@count + 1)

END

DECLARE @SPLITROW TABLE

(ORDER_NUMBER INT

, PRODUCT_ID INT

, QTY INT

, VALUE MONEY

)

INSERT INTO @SPLITROW

VALUES (2345, 34567, 3, 15.00)

INSERT INTO @SPLITROW

VALUES (2346, 34568, 5, 15.00)

INSERT INTO @SPLITROW

VALUES (2347, 34569, 15, 234.00)

SELECT ORDER_NUMBER, PRODUCT_ID, QTY/QTY AS QTY, VALUE/QTY AS VALUE

FROM @SPLITROW S

INNER JOIN @TALLY T

ON T.ID BETWEEN 1 AND S.QTY

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

You can use "on-fly" table which can provide you with continues numbers. Any would do, I've used sys.columns... :

SELECT ORDER_NUMBER, PRODUCT_ID, 1 QTY, VALUE/QTY VALUE

FROM @SPLITROW sr

JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN

FROM sys.columns) AS oft

ON oft.RN <= QTY

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

kate-395233 (5/3/2012)
Thank you very much!
This is how I modified it (I don't have access to the master db) - and added more rows
DECLARE @count INT
SET @count = 1
DECLARE @TALLY TABLE
(ID INT
, PRIMARY KEY (ID)
)
WHILE (@count < 100440)
BEGIN
INSERT INTO @TALLY
SELECT @COUNT
SET @count = (@count + 1)
END
DECLARE @SPLITROW TABLE
(ORDER_NUMBER INT
, PRODUCT_ID INT
, QTY INT
, VALUE MONEY
)
INSERT INTO @SPLITROW
VALUES (2345, 34567, 3, 15.00)
INSERT INTO @SPLITROW
VALUES (2346, 34568, 5, 15.00)
INSERT INTO @SPLITROW
VALUES (2347, 34569, 15, 234.00)
SELECT ORDER_NUMBER, PRODUCT_ID, QTY/QTY AS QTY, VALUE/QTY AS VALUE
FROM @SPLITROW S
INNER JOIN @TALLY T
ON T.ID BETWEEN 1 AND S.QTY

You have used one of the slowest possible ways to populate tally table!

1. Use tamp-table instead of table variable

2. Populate it using set-based query, something like:

DECLARE @mxq INT

SELECT @mxq = MAX(QTY) FROM @SPLITROW

SELECT TOP (@mxq) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID

INTO #TALLY

FROM sys.columns s1 CROSS JOIN sys.columns s2

-- you can add unique clustered index as:

CREATE UNIQUE CLUSTERED INDEX ixc_#tally ON #tally(ID)

--but I think it will be good enough without it

3. You will get even better result if create dedicated permanent tally table in your database! You will be able to use it in many other queries (eg. string split ones)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

kate-395233 (5/3/2012)
Thank you very much!
This is how I modified it (I don't have access to the master db) - and added more rows
DECLARE @count INT
SET @count = 1
DECLARE @TALLY TABLE
(ID INT
, PRIMARY KEY (ID)
)
WHILE (@count < 100440)
BEGIN
INSERT INTO @TALLY
SELECT @COUNT
SET @count = (@count + 1)
END
DECLARE @SPLITROW TABLE
(ORDER_NUMBER INT
, PRODUCT_ID INT
, QTY INT
, VALUE MONEY
)
INSERT INTO @SPLITROW
VALUES (2345, 34567, 3, 15.00)
INSERT INTO @SPLITROW
VALUES (2346, 34568, 5, 15.00)
INSERT INTO @SPLITROW
VALUES (2347, 34569, 15, 234.00)
SELECT ORDER_NUMBER, PRODUCT_ID, QTY/QTY AS QTY, VALUE/QTY AS VALUE
FROM @SPLITROW S
INNER JOIN @TALLY T
ON T.ID BETWEEN 1 AND S.QTY

Don't use a WHILE loop to create a tally table. . . it's the slowest method.

If you don't have access to master database, you could instead use a CTE tally table: -

--SAMPLE DATA

DECLARE @SPLITROW TABLE

(ORDER_NUMBER INT

, PRODUCT_ID INT

, QTY INT

, VALUE MONEY

)

INSERT INTO @SPLITROW

VALUES (2345, 34567, 3, 15.00)

INSERT INTO @SPLITROW

VALUES (2346, 34568, 5, 15.00)

INSERT INTO @SPLITROW

VALUES (2347, 34569, 15, 234.00);

--ACTUAL SOLUTION

WITH CTE1(N) AS (SELECT N FROM (VALUES(1),(1))a(N)),

CTE2(N) AS (SELECT 1 FROM CTE1 x, CTE1 y),

CTE3(N) AS (SELECT 1 FROM CTE2 x, CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x, CTE3 y),

CTE5(N) AS (SELECT 1 FROM CTE4 x, CTE4 y),

CTETally(N) AS (SELECT TOP 100440 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE5 x, CTE5 y)

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE

FROM @SPLITROW s

INNER JOIN CTETally t ON t.n <= s.QTY

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kate-395233 Say Hey Kid Points: 694 More actions · Answer 6

Thanks all.

I probably will have use for a tally table in future so have gone for that option.

And I didn't use the While Loop when generating it 😉

nishav2 SSCrazy Points: 2224 More actions · Answer 7

Hi ,

I am having similar kind of requirement.

I have a table.

create table dbo.test(ID int,Price decimal(18,9), Qty decimal(18,9))

Insert into dbo.test values(1,5000,5000000)

Insert into dbo.test values(2,3000,50000000)

Insert into dbo.test values(3,100,50000)

I need the output in the below format

ID PRICE QTY

1 5000 5000000

2-1 30 5000000

2-2 30 5000000

2-3 30 5000000

2-4 30 5000000

2-5 30 5000000

2-6 30 5000000

2-7 30 5000000

2-8 30 5000000

2-9 30 5000000

2-10 30 5000000

3 100 50000

So the Qty limit is : 5,000000

First record is equal to the limit.So it can appear in the same format as stored in table.

In Second Record -Qty is 10 times greater than our limit.So all the columns are splitted as shown above

Third Row - It is within the limit we have set for Qty.So it can appear in the same format as stored in table.

Any help is appreciated.

Thanks

Nisha V Krishnan

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

nishav2 (10/1/2014)
Hi ,
I am having similar kind of requirement.
I have a table.
create table dbo.test(ID int,Price decimal(18,9), Qty decimal(18,9))
Insert into dbo.test values(1,5000,5000000)
Insert into dbo.test values(2,3000,50000000)
Insert into dbo.test values(3,100,50000)
I need the output in the below format
ID PRICE QTY
1 5000 5000000
2-1 30 5000000
2-2 30 5000000
2-3 30 5000000
2-4 30 5000000
2-5 30 5000000
2-6 30 5000000
2-7 30 5000000
2-8 30 5000000
2-9 30 5000000
2-10 30 5000000
3 100 50000
So the Qty limit is : 5,000000
First record is equal to the limit.So it can appear in the same format as stored in table.
In Second Record -Qty is 10 times greater than our limit.So all the columns are splitted as shown above
Third Row - It is within the limit we have set for Qty.So it can appear in the same format as stored in table.
Any help is appreciated.
Thanks
Nisha V Krishnan

You also hijacked another thread in the sql7, 2000 forum. I would recommend starting your own thread and put it in the appropriate version of sql you are using. The answers and techniques will vary greatly if you are on 2000.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

bugg SSCertifiable Points: 7567 More actions · Answer 9

Mark Cowne (5/3/2012)
Join to a numbers/tally table
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Hey Guys, I'm looking to do a similar thing ,avoiding loops, is this wise to use in a production environment ? hook into the system tables?

Thanks

B

Neil Burton SSC-Insane Points: 23008 More actions · Answer 10

bugg (1/30/2015)
Mark Cowne (5/3/2012)
Join to a numbers/tally table
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Hey Guys, I'm looking to do a similar thing ,avoiding loops, is this wise to use in a production environment ? hook into the system tables?
Thanks
B

You hijacked another thread in the sql7, 2000 forum. I would recommend starting your own thread and put it in the appropriate version of sql you are using. The answers and techniques will vary greatly if you are on 2000.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

bugg (1/30/2015)
Mark Cowne (5/3/2012)
Join to a numbers/tally table
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Hey Guys, I'm looking to do a similar thing ,avoiding loops, is this wise to use in a production environment ? hook into the system tables?
Thanks
B

Read this article. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

cnitzsche SSC Journeyman Points: 97 More actions · Answer 12

Hi All,

When recursive data extracts are required, try CTE:

DECLARE @SPLITROW TABLE (ORDER_NUMBER INT , PRODUCT_ID INT , QTY INT , VALUE MONEY ) --Populate the Data table INSERT INTO @SPLITROW VALUES (2345, 34567, 3, 15.00) , (2346, 34568, 5, 15.00) , (2347, 34569, 15, 234.00)

Select * from @SPLITROW

--Here the Recursive CTE: The QTY is reducing untill it reaches "1" ;With Splitter as ( Select ORDER_NUMBER, PRODUCT_ID, Unit = 1 , QTY , UnitPrice = Convert(MONEY,VALUE / QTY * 1.0) from @SPLITROW union all Select ORDER_NUMBER, PRODUCT_ID, Unit = 1 , QTY - 1 , UnitPrice from Splitter where Qty > 1 ) --Need to insert into a table? add line : Insert into tablename (Colums...) Select ORDER_NUMBER, PRODUCT_ID, Unit, unitPrice from Splitter Order by ORDER_NUMBER, PRODUCT_ID

--Note: Ordering is required only if you need the output in a sensible order.

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

cnitzsche - Wednesday, February 8, 2017 11:03 PM
Hi All,
When recursive data extracts are required, try CTE:
DECLARE @SPLITROW TABLE (ORDER_NUMBER INT , PRODUCT_ID INT , QTY INT , VALUE MONEY ) --Populate the Data table INSERT INTO @SPLITROW VALUES (2345, 34567, 3, 15.00) , (2346, 34568, 5, 15.00) , (2347, 34569, 15, 234.00)
Select * from @SPLITROW
--Here the Recursive CTE: The QTY is reducing untill it reaches "1" ;With Splitter as ( Select ORDER_NUMBER, PRODUCT_ID, Unit = 1 , QTY , UnitPrice = Convert(MONEY,VALUE / QTY * 1.0) from @SPLITROW union all Select ORDER_NUMBER, PRODUCT_ID, Unit = 1 , QTY - 1 , UnitPrice from Splitter where Qty > 1 ) --Need to insert into a table? add line : Insert into tablename (Colums...) Select ORDER_NUMBER, PRODUCT_ID, Unit, unitPrice from Splitter Order by ORDER_NUMBER, PRODUCT_ID
--Note: Ordering is required only if you need the output in a sensible order.

This is still pretty much the same thing as a loop. It has the appearance of being set based but a recursive cte like this is a hidden RBAR operation. Take a look at this article from Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/74118/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

Sean Lange - Thursday, February 9, 2017 7:03 AM
cnitzsche - Wednesday, February 8, 2017 11:03 PM
Hi All,
When recursive data extracts are required, try CTE:
DECLARE @SPLITROW TABLE (ORDER_NUMBER INT , PRODUCT_ID INT , QTY INT , VALUE MONEY ) --Populate the Data table INSERT INTO @SPLITROW VALUES (2345, 34567, 3, 15.00) , (2346, 34568, 5, 15.00) , (2347, 34569, 15, 234.00)
Select * from @SPLITROW
--Here the Recursive CTE: The QTY is reducing untill it reaches "1" ;With Splitter as ( Select ORDER_NUMBER, PRODUCT_ID, Unit = 1 , QTY , UnitPrice = Convert(MONEY,VALUE / QTY * 1.0) from @SPLITROW union all Select ORDER_NUMBER, PRODUCT_ID, Unit = 1 , QTY - 1 , UnitPrice from Splitter where Qty > 1 ) --Need to insert into a table? add line : Insert into tablename (Colums...) Select ORDER_NUMBER, PRODUCT_ID, Unit, unitPrice from Splitter Order by ORDER_NUMBER, PRODUCT_ID
--Note: Ordering is required only if you need the output in a sensible order.
This is still pretty much the same thing as a loop. It has the appearance of being set based but a recursive cte like this is a hidden RBAR operation. Take a look at this article from Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/74118/

It can actually be worse than a loop. If Jeff's article isn't enough evidence, here's another example written by a very handsome guy: http://www.sqlservercentral.com/articles/set-based+loop/127670/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2