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?

asked 11 Oct, 18:34

Rolle's gravatar image

Rolle
512364353
accept rate: 0%

edited 14 Oct, 05:24

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

(14 Oct, 03:41) Volker Barth

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

(14 Oct, 05:28) Rolle
Replies hidden

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.

(14 Oct, 06:39) Volker Barth
Comment Text Removed

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?

(14 Oct, 08:40) Rolle
Replies hidden

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

(14 Oct, 12:07) Volker Barth

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

answered 14 Oct, 12:50

Volker%20Barth's gravatar image

Volker Barth
34.6k337491729
accept rate: 33%

edited 14 Oct, 12:53

Thanks, this helped me.

(15 Oct, 10:50) Rolle
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 11 Oct, 18:34

question was seen: 201 times

last updated: 15 Oct, 10:50