Pivot action to put sub-rows as multiple column fields.

[denimined]

Yes. I know. This is incredibly dumb, but a user is a user is a user.

Given master products composed of 1-n sub-products, want a report where the single line for the master product contains up to 5 of the composite sub-products on that line.
e.g.
sub-products
leg
seat
back
top

master product
stool - composed of leg, leg, leg, seat
chair - composed of leg, leg, leg, leg, seat, back
table - composed of leg, leg, leg, leg, top

Here is a trivial data set that has the information, along with a view to access the relation.

CREATE TABLE MProduct ( Recno int identity, 
                        Name varchar(20), 
                        CONSTRAINT PK_MProduct PRIMARY KEY NONCLUSTERED (Recno) 
                      );
CREATE TABLE SProduct ( Recno int identity,
                        Name varchar(20), 
                        CONSTRAINT PK_SProduct PRIMARY KEY NONCLUSTERED (Recno) 
                      );
CREATE TABLE Links ( Recno int identity, 
                     MP int not null, 
					 SP int not null, 
                     CONSTRAINT PK_Links PRIMARY KEY NONCLUSTERED (Recno), 
					 CONSTRAINT FK_MP FOREIGN KEY (MP) REFERENCES MProduct (Recno) ON DELETE CASCADE,
					 CONSTRAINT FK_SP FOREIGN KEY (SP) REFERENCES SProduct (Recno) ON DELETE CASCADE
                   );
GO

INSERT INTO MProduct (Name) VALUES ('Table')
INSERT INTO MProduct (Name) VALUES ('Chair')
INSERT INTO MProduct (Name) VALUES ('Stool')
INSERT INTO SProduct (Name) VALUES ('Leg')
INSERT INTO SProduct (Name) VALUES ('Seat')
INSERT INTO SProduct (Name) VALUES ('Back')
INSERT INTO SProduct (Name) VALUES ('Top')
GO

INSERT INTO LINKS (MP, SP) VALUES (1, 1)
INSERT INTO LINKS (MP, SP) VALUES (1, 1)
INSERT INTO LINKS (MP, SP) VALUES (1, 1)
INSERT INTO LINKS (MP, SP) VALUES (1, 1)
INSERT INTO LINKS (MP, SP) VALUES (1, 4)

INSERT INTO LINKS (MP, SP) VALUES (2, 1)
INSERT INTO LINKS (MP, SP) VALUES (2, 1)
INSERT INTO LINKS (MP, SP) VALUES (2, 1)
INSERT INTO LINKS (MP, SP) VALUES (2, 1)
INSERT INTO LINKS (MP, SP) VALUES (2, 2)
INSERT INTO LINKS (MP, SP) VALUES (2, 3)

INSERT INTO LINKS (MP, SP) VALUES (3, 1)
INSERT INTO LINKS (MP, SP) VALUES (3, 1)
INSERT INTO LINKS (MP, SP) VALUES (3, 1)
INSERT INTO LINKS (MP, SP) VALUES (3, 2)
GO

CREATE VIEW Products AS 
SELECT M.Name as Product, 
       S.Name as Component
FROM LINKS AS L 
JOIN MProduct AS M ON M.Recno= L.MP 
JOIN SProduct as S ON S.Recno = L.SP

GO

So while the Product view contains what each master product is composed of, the desired report
[SELECT DISTINCT * FROM PRODUCTS]

would show something like:

Product Cmp1 Cmp2 Cmp3
Table   Leg  Top
Chair   Leg  Seat Back
Stool   Leg  Seat

Note that if there are more components than have repeating columns for, the extra components are ignored.

I keep looking at PIVOT for a solution, but I may be being blinded by my thoughts.

Thx all.

 

[SkipVought]

Hi,

What you seem to have is a single indentured bill-of-material, (as opposed to a multi-indentured BOM.)

You don't usually see a BOM 'pivoted' as you have indicated. Some BOM items can have dozens if not hundreds of components, that does not lend itself to a 'pivot' as you describe!

Normally, each item component has a number of properties as well, which further complicates a 'pivot' report.

Of course, in actual terms, a pivot presents a SUMMARY in terms of aggregations, the data of which is not present in your 'pivot!'

So I'm at a loss to advise you on your 'pivot' conundrum.

 

[SkipVought]

I did think of an application for listing the components in a STRING, when generating an assembly document at a particular assembly operation. You might have one component at an operation or many components, that need to be listed. But I'd see that list included in an identifiable field that might be labeled Instructions or Materials for this operation.

 

[denimined]

OK. I'm pretty sure PIVOT is a wild goose chase. Any other suggestions? [OVER?]