0

I have the following code, I want to transpose multiple rows to one column.

This is an example of how my data looks like:

DocNum    ItemCode  Item Name
-----------------------------------
40008      100208   Desc of 100208  /*parts*/
40008      100209   Desc of 100208  /*parts*/
40008      200845   Desc of 200845  /* tire*/
40008      320361   Desc of 320361  /* disc*/
40008      400511   Desc of 400511  /* rim*/

I am looking a result as shown below

itemcode   rim/disc                  tire/parts    
------------------------------------------------
40008      400511-Desc of 400511     200845-Desc of 200845 
           320361-Desc of 320361     100208-Desc of 100208  
                                     100209-Desc of 100208

This is the SQL code I tried:

select top 10  
    T0.[DocNum], T0.[OriginNum], T0.[ItemCode], T0.[ProdName],     
    T2.[CardName], T0.[Status], T4.[U_Routing], T0.[PlannedQty], 
    T0.[CmpltQty], T0.[PostDate], T0.[DueDate],T1.[ItemCode], 
    T3.[ItemName], T0.[U_Work_Center], T0.[CreateDate]  
from  
    [SERVERNAME]..[DATABASE].[OWOR] as T0 
inner join 
    [SERVERNAME]..[DATABASE].[WOR1] T1 on T0.[DocEntry] = T1.[DocEntry] 
inner join 
    [SERVERNAME]..[DATABASE].[OCRD] T2 on T0.[CardCode] = T2.[CardCode] 
inner join
    [SERVERNAME]..[DATABASE].[OITM] T3 on T1.[ItemCode] = T3.[ItemCode] 
inner join
    [SERVERNAME]..[DATABASE]. [OITM] T4 on T0.[ItemCode] = T4.[ItemCode]
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kinfe
  • 305
  • 1
  • 3
  • 14
  • 4
    Have you search about Pivot/Unpivot before asking? – Ilyes Jun 06 '19 at 20:03
  • https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server/15931734 You can have a look at this. – Farhan Jun 06 '19 at 20:04
  • 1
    You have simplified your data so much nobody can tell much of anything. From your data it seems there is no way to know what is what. There is nothing to distinguish them apart. And do yourself a favor, try formatting your queries so they are legible. Might also want to take a peek at this. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 – Sean Lange Jun 06 '19 at 20:05
  • Ok. Thank you all for help. – Kinfe Jun 06 '19 at 20:08

1 Answers1

0

As commented in your question you need to work with PIVOT tables, please see the following link.

Using your data here is an example:

CREATE TABLE ITEMS (DocNum INT, ItemCode NVARCHAR(MAX), ItemName NVARCHAR(MAX), ItemDesc NVARCHAR(MAX));

INSERT INTO ITEMS (DocNum, ItemCode, ItemName, ItemDesc)
VALUES (40008, '100208', 'Desc of 100208', 'Parts');

INSERT INTO ITEMS (DocNum, ItemCode, ItemName, ItemDesc)
VALUES (40008, '100209', 'Desc of 100209', 'Parts');

INSERT INTO ITEMS (DocNum, ItemCode, ItemName, ItemDesc)
VALUES (40008, '200845', 'Desc of 200845', 'Tire');

INSERT INTO ITEMS (DocNum, ItemCode, ItemName, ItemDesc)
VALUES (40008, '320361', 'Desc of 320361', 'Disc');

INSERT INTO ITEMS (DocNum, ItemCode, ItemName, ItemDesc)
VALUES (40008, '400511', 'Desc of 400511', 'Rim');

Here is an example of a pivoted table:

SELECT 
  DocNum AS [DocNum], [Parts],[Tire],[Disc],[Rim]
FROM
  (
    SELECT 
      DocNum, ItemName, ItemDesc
    FROM ITEMS
   ) AS SOURCE
PIVOT
(
  MAX(ItemName) FOR ItemDesc IN ([Parts],[Tire], [Disc], [Rim])
)  AS Pivoted;

You may notice that the column names have the same same of the data of the column ItemDesc. For this simple scenario is not a hard task to put the column names manually, you probably need to create a stored procedure to generate your pivoted table dynamically which you can see how to do in the following link.

Here you can test this script: http://www.sqlfiddle.com/#!18/45f81/23/0 and see the expected results. Good luck!

Flavio Francisco
  • 755
  • 1
  • 8
  • 21