Repeat dataset by integer value in another table.

Question

Repeat dataset by integer value in another table.

Kelvin Phayre

Ten Centuries

Points: 1358
More actions
August 23, 2013 at 4:37 am

#281560

I have a report that prints a manifest which is loaded onto n number of pallets. I have a lookup for the manifest that returns 1 row detailing the number of pallets lets say 2.
Simple report list 10 parts & quantities on one page.
I want the same report to print two pages with exactly the same details but on page 1 I want Pallet 1 of 2 and on page 2 I want Pallet 2 of 2
Data
CREATE TABLE [dbo].[BayPallets] (
[BAY] char(10) NOT NULL,
[PALLETS]int NOT NULL
)
GO
INSERT INTO [dbo].[BayPallets]([BAY], [PALLETS])
VALUES('E010 ', 1)
GO
INSERT INTO [dbo].[BayPallets]([BAY], [PALLETS])
VALUES('E020 ', 2)
GO
INSERT INTO [dbo].[BayPallets]([BAY], [PALLETS])
VALUES('E030 ', 3)
GO
CREATE TABLE [dbo].[BAYSTOCK] (
[BAY] char(10) NOT NULL,
[PART]char(20) NOT NULL,
[QTY] float NULL
)
GO
INSERT INTO [dbo].[BAYSTOCK]([BAY], [PART], [QTY])
VALUES('E020 ', '0610073007 ', 6.0)
GO
INSERT INTO [dbo].[BAYSTOCK]([BAY], [PART], [QTY])
VALUES('E020 ', '0613009020 ', 18.0)
GO
INSERT INTO [dbo].[BAYSTOCK]([BAY], [PART], [QTY])
VALUES('E020 ', '0613010020 ', 18.0)
GO
INSERT INTO [dbo].[BAYSTOCK]([BAY], [PART], [QTY])
VALUES('E020 ', '0613011020 ', 6.0)
GO
INSERT INTO [dbo].[BAYSTOCK]([BAY], [PART], [QTY])
VALUES('E020 ', '0613013020 ', 6.0)
GO

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Thanks for posting sample data, Kelvin. What do you want the output to look like?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kelvin Phayre Ten Centuries Points: 1358 More actions · Answer 2

BAY PALLET BAY PART QTY PALLETS

---------- --------- ---------- -------------------- ------ ----------

E020 1 E020 0610073007 6 2

E020 1 E020 0610073007 6 3

E020 1 E020 0610073007 6 4

E020 1 E020 0613009020 18 2

E020 1 E020 0613009020 18 3

E020 1 E020 0613009020 18 4

E020 1 E020 0613010020 18 2

E020 1 E020 0613010020 18 3

E020 1 E020 0613010020 18 4

E020 1 E020 0613011020 6 2

E020 1 E020 0613011020 6 3

E020 1 E020 0613011020 6 4

E020 1 E020 0613013020 6 2

E020 1 E020 0613013020 6 3

E020 1 E020 0613013020 6 4

E020 2 E020 0610073007 6 2

E020 2 E020 0610073007 6 3

E020 2 E020 0610073007 6 4

E020 2 E020 0613009020 18 2

E020 2 E020 0613009020 18 3

E020 2 E020 0613009020 18 4

E020 2 E020 0613010020 18 2

E020 2 E020 0613010020 18 3

E020 2 E020 0613010020 18 4

E020 2 E020 0613011020 6 2

E020 2 E020 0613011020 6 3

E020 2 E020 0613011020 6 4

E020 2 E020 0613013020 6 2

E020 2 E020 0613013020 6 3

E020 2 E020 0613013020 6 4

E020 3 E020 0610073007 6 2

E020 3 E020 0610073007 6 3

E020 3 E020 0610073007 6 4

E020 3 E020 0613009020 18 2

E020 3 E020 0613009020 18 3

E020 3 E020 0613009020 18 4

E020 3 E020 0613010020 18 2

E020 3 E020 0613010020 18 3

E020 3 E020 0613010020 18 4

E020 3 E020 0613011020 6 2

E020 3 E020 0613011020 6 3

E020 3 E020 0613011020 6 4

E020 3 E020 0613013020 6 2

E020 3 E020 0613013020 6 3

E020 3 E020 0613013020 6 4

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

From the sample data??

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden