Help with Query - Split QTY into Single Rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Query - Split QTY into Single Rows

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2011-11-09 : 12:44:50

Hi,

I have this data that I want to split anything with a quantity greater than 1 into individual rows in the result set. Any help is appreciated and I included Sample data below. Thanks.

I have this:


ID	PID	PartNo	Desc	QTY
16	1	124434	POWER PACK ASSY	1
17	16	125432	FRAME ASSY, POWER PACK	1
18	17	129430	FRAME WELDED	1
19	17	803053	BOLT,EYE	4
20	16	7423275	CONTROLLER BTR-55	1
21	16	12212374 SHORTING RELAY INSTL	1
22	21	12276372 SHORTING RELAY ASSY	1
23	22	12452373 BRACKET ASSY,RELAY AND FUSE	1
24	22	12470366 CABLE ASSY RELAY & FUSE	1
25	22	7726329	FUSEHOLDER SUBASSEMBLY	1

And I want this:


ID	PID	PartNo	Desc	QTY
16	1	124434	POWER PACK ASSY	1
17	16	125432	FRAME ASSY, POWER PACK	1
18	17	129430	FRAME WELDED	1
19	17	803053	BOLT,EYE	1
19	17	803053	BOLT,EYE	1
19	17	803053	BOLT,EYE	1
19	17	803053	BOLT,EYE	1
20	16	7423275	CONTROLLER BTR-55	1
21	16	12212374 SHORTING RELAY INSTL	1
22	21	12276372 SHORTING RELAY ASSY	1
23	22	12452373 BRACKET ASSY,RELAY AND FUSE	1
24	22	12470366 CABLE ASSY RELAY & FUSE	1
25	22	7726329	FUSEHOLDER SUBASSEMBLY	1

Sample data to work with. I am not concerned with the final QTY field only that they are accurately split into rows.


CREATE TABLE [dbo].[JB_TestData](
	[ID] [int] NOT NULL,
	[PID] [int] NOT NULL,
	[PartNo] [varchar](55) NOT NULL,
	[Desc] [varchar](255) NOT NULL,
	[Qty] [int] NOT NULL
) ON [PRIMARY]


Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (16,	1,	124435-1,	'POWER PACK ASSY',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (17,	16,	125433-1,	'FRAME ASSY, POWER PACK' ,1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (18,	17,	129433-3,	'FRAME WELDED',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (19,	17,	803060-7,	'BOLT,EYE',	4)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (20,	16,	7423280-5,	'CONTROLLER BTR-55',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (21,	16,	12212375-1,	'SHORTING RELAY INSTL',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (22,	21,	12276375-3,	'SHORTING RELAY ASSY',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (23,	22,	12452374-1,	'BRACKET ASSY,RELAY AND FUSE',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (24,	22,	12470367-1,	'CABLE ASSY RELAY & FUSE',	1)
Insert Into dbo.JB_TestData (ID, PID, PartNo, [Desc], Qty)
Values (25,	22,	7726330-1,	'FUSEHOLDER SUBASSEMBLY',	1)

Select *
From dbo.JB_TestData

Drop Table dbo.JB_TestData

JBelthoff
› As far as myself... I do this for fun!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 12:48:10

make query like


Select t.ID, t.PID, t.PartNo, t.[Desc],1 AS Qty
From dbo.JB_TestData t
CROSS JOIN master..spt_values v
WHERE v.number BETWEEN 1 AND t.Qty
AND v.type ='p'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JBelthoff
Posting Yak Master

173 Posts

Posted - 2011-11-09 : 12:53:02

Excellent Thank you!

JBelthoff
› As far as myself... I do this for fun!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 13:02:47

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources