Rotate a table

[former_SQLA_member1694869]

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?

[VolkerBarth]

So do you expect us to study these long lines of code and samples and to compare the result set ourselves to find out what the difference between your actual and your desired result set is? Some words to clarify or an attempt to break down into a simple sample would be worthwhile here IMHO...

[former_SQLA_member1694869]

Excuse me for being unclear. I have downplayed my examples and tried to be clearer in my question

[VolkerBarth]

Hm, I guess I did not suggest that you should delete your previous attempt to generate a fitting restult set and its outcome. It usually helps as a starting point, and it also might make clear what problem you face... BTW, what version do you use? Note that v17 does have a PIVOT feature.

[former_SQLA_member1694869]

I use version 17. Have tried with Pivot, but only gets to work with ONE column. How do I apply it to work with multiple columns?

[VolkerBarth]

Sorry, I have no idea why the result set should be like that. While I can understand that different ComponentPartCode values have to be displayed as separate columns (thereby pivoting), I don't understand why Component6 and Component7 (sharing the same StorageNumber) appear in different rows. That's what I tried to tell: Without some details about the logic behind your desired result set it's difficult (and quite useless) to guess...

[VolkerBarth · Accepted solution]

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".

[former_SQLA_member1694869 · Accepted solution]

Thanks, this helped me.