Convert Single Row into Multiple Records (rows) 1

[RJL1]

I have the query below which returns 2 records. One of the records is for 1 case and the other for 2 cases. I need the record for 2 cases broken into 2 rows each for one case

SELECT
-----------------------------------------------------------
WI.REFERENCE_ID,
WI.ITEM,
WI.CONVERTED_QTY AS 'QTY',
WI.CONVERTED_QTY_UM AS 'UOM',
WI.FROM_LOC
-----------------------------------------------------------
FROM
WORK_INSTRUCTION WI WITH(NOLOCK)
-----------------------------------------------------------
WHERE
WI.INSTRUCTION_TYPE = 'DETAIL' AND
WI.INTERNAL_NUM = 118835

currect outeput of the query (above)

REFERENCE ID     ITEM        QTY   UOM   FROM LOCATION
------------     -----       ----  ---   -------------
TEST0000001      3125-05     2     CS    001-006-AB-03
TEST0000001      6255-01     1     CS    006-029

Desired output. I need one record for each CS for item 3125-05.

REFERENCE ID     ITEM        QTY   UOM   FROM LOCATION
------------     -----       ----  ---   -------------
TEST0000001      3125-05     1     CS    001-006-AB-03
TEST0000001      3125-05     1     CS    001-006-AB-03
TEST0000001      6255-01     1     CS    006-029

I need each item that has a qty grated than 1 to be broken down into a record for each case so if the qty = 5 then I would need 5 records each for 1 CS.

Any help is appreciated

Thanks
RJL

 

[gmmastros]

Do you have a numbers table in your database? If so, it would make this relatively easy to do.

In my database I have a Numbers table with a single column named Num. There are 1 million rows in this table with the Num value incrementing by one for each row.

To create this table in your database, open a query window and run the following code:

Create Table Numbers(Num Int Identity(1,1) Primary Key)
go
Insert Into Numbers Default Values
go 1000000

The code to create the numbers table and fill it with data may take a couple minutes to run. Once it's done, you can use it for your query like this:

SELECT
-----------------------------------------------------------
WI.REFERENCE_ID,
WI.ITEM,
[!]1[/!] AS 'QTY',
WI.CONVERTED_QTY_UM AS 'UOM',
WI.FROM_LOC
-----------------------------------------------------------
FROM
WORK_INSTRUCTION WI WITH(NOLOCK)
[!]Inner Join Numbers On WI.CONVERTED_QTY >= Numbers.Num[/!]
-----------------------------------------------------------
WHERE
WI.INSTRUCTION_TYPE = 'DETAIL' AND
WI.INTERNAL_NUM = 118835

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Thanks

I made a few changes to make it work but it is what I was looking for. The numbers table is great for future use

Changes

Left Outer Join Numbers On WI.CONVERTED_QTY >= Numbers.Num

instead of inner join

Thanks