Databases
StackOverflow2013
Postsdbo
POneed help in writing pivot query to select columns from different select statements
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POneed help in writing pivot query to select columns from different select statements
    text
    copied!<p>I am using SQL Server 2012, i need help in getting the following output. My table structure is as follows:</p> <pre><code>declare @TestTable table (MaterialCode char(5), ItemCode char(5), ItemName varchar(50)) insert into @TestTable values ('AA-01', 'A0001', 'iPhone') insert into @TestTable values ('AA-02', 'A0001', 'iPad') insert into @TestTable values ('AA-03', 'A0001', 'iPod') insert into @TestTable values ('AA-01', 'B0001', 'Galaxy Tab') insert into @TestTable values ('AA-02', 'B0001', 'Galaxy Note') insert into @TestTable values ('AA-01', 'C0001', 'Nokia Lumnia') insert into @TestTable values ('AA-02', 'C0001', 'Motorola') insert into @TestTable values ('AA-03', 'C0001', 'Samsung S3') insert into @TestTable values ('AA-04', 'C0001', 'Sony') --select * from @TestTable select MaterialCode, ItemCode as [A_ItemCode], ItemName as [A_ItemName] from @TestTable where ItemCode='A0001' select MaterialCode, ItemCode as [B_ItemCode], ItemName as [B_ItemName] from @TestTable where ItemCode='B0001' select MaterialCode, ItemCode as [C_ItemCode], ItemName as [C_ItemName] from @TestTable where ItemCode='C0001' </code></pre> <p>And the output i need should produced from the above three select statements, which should be as follows:</p> <p><img src="https://i.stack.imgur.com/xpRV8.png" alt="enter image description here"></p> <p>As you can see, when there is no record, NULLs are displayed there. Can anyone help me getting this output. TIA.</p> <p><strong>EDIT</strong></p> <p>@JohnLBevan, I tried your pivot approach and when I have another record which is has ItemCode = D0001</p> <pre><code>insert into @TestTable values ('AA-05', 'D0001', 'Test1') </code></pre> <p>now even that record is being displayed as</p> <pre><code>AA-05 NULL NULL NULL NULL NULL NULL </code></pre> <p>How to avoid these type of records.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload