cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

Rotate a table

0 Kudos
1,501

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?

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor
0 Kudos

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:

  • The columns for the descripions are named "Component_2100_Description", not "ComponentDescription_2100". If the resulting columns do not fit, you can easily use aliases in the SELECT list to name them as you prefer.
  • As stated in my last comment, "Component7" will be part of the second row, listed together with "Component6".
0 Kudos

Thanks, this helped me.

Answers (0)