on 2019 Oct 11 7:34 PM
I need to rotate this table.
CREATE TABLE tblComponentList( Material nvarchar(16) not null, MaterialDescription nvarchar(50) not null, ComponentPartCode integer null, Component nvarchar(16) null, ComponentDescription nvarchar(50) null, StorageNumber integer null, Operation nvarchar(100) null); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2100, 'Component1', 'Description component 1', 1, '10+11+12'); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2300, 'Component2', 'Description component 2', 1, '10+11+12'); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2400, 'Component3', 'Description component 3', 1, '10+11+12'); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2100, 'Component4', 'Description component 4', 2, '11'); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2300, 'Component2', 'Description component 2', 2, '11'); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2400, 'Component6', 'Description component 6', 2, '11'); insert into tblComponentList values ('3222305797', 'Cylinder Bracket', 2400, 'Component7', 'Description component 7', 2, '11');
I have tried different sql's...without luck There are max 4 different ComponentPartCodes.
I wish this result:
---Material---MaterialDescription---StorageNumber---Operation---Component_2100---Component_2200---Component_2300---Component_2400---ComponentDescription_2100---ComponentDescription_2200---ComponentDescription_2300---ComponentDescription_2400 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --3222305797--Cylinder Bracket----------1-----------10+11+12-----Component1----------null----------Component2-------Component3--------Description component 1--------------null--------------Description component 2-----Description component 3- --3222305797--Cylinder Bracket----------2--------------11--------Component4----------null----------Component2-------Component6--------Description component 4--------------null--------------Description component 2-----Description component 6- --3222305797--Cylinder Bracket----------2--------------11----------null--------------null--------------null---------Component7---------------null--------------------------null----------------------null----------------Description component 7-
How?
Request clarification before answering.
I guess the following using PIVOT may help further:
select * from (select Material, MaterialDescription, StorageNumber, Operation, Component, ComponentPartCode, ComponentDescription from tblComponentList) DT pivot ( -- the first aggregate is unnamed, -- leading to the resulting column named like the pivot column, -- e.g. "Component_2100" list(Component), -- the second aggregate is named "Description", -- so the resulting column name has an added "_Description", -- e.g. "Component_2100_Description" list(ComponentDescription) as Description for ComponentPartCode in -- specifying aliases for the pivot columns (2100 as Component_2100, 2200 as Component_2200, 2300 as Component_2300, 2400 as Component_2400) ) PV order by StorageNumber;
This lead to a result set like in your question with two differences:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
33 | |
22 | |
17 | |
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.