How can i transpose multiple row and insert record into another table

Question

I have the following code which transpose multiple rows into one based on the CUSTOMER and ship date but after i get the transposed results i want to insert the result into another table.

Here is the destination table

    CREATE TABLE [dbo].[PACKSLIP](
        [AUTO PACK SLIP #] [int] NOT NULL, --Auto Increment field starts 598

        [1. DESCRIPTION] [nvarchar](977) NULL,
        [2. DESCRIPTION] [nvarchar](977) NULL,
        [3. DESCRIPTION] [nvarchar](977) NULL,
        [4. DESCRIPTION] [nvarchar](977) NULL,
        [5. DESCRIPTION] [nvarchar](377) NULL,
        [6. DESCRIPTION] [nvarchar](377) NULL,
        [7. DESCRIPTION] [nvarchar](377) NULL,
        [8. DESCRIPTION] [nvarchar](377) NULL,
        [9. DESCRIPTION] [nvarchar](377) NULL,
        [10. DESCRIPTION] [nvarchar](377) NULL,
        [1. QTY] [nvarchar](255) NULL,
        [2. QTY] [nvarchar](255) NULL,
        [3. QTY] [nvarchar](255) NULL,
        [4. QTY] [nvarchar](255) NULL,
        [5. QTY] [nvarchar](255) NULL,
        [6. QTY] [nvarchar](255) NULL,
        [7. QTY] [nvarchar](255) NULL,
        [8. QTY] [nvarchar](255) NULL,
        [9. QTY] [nvarchar](255) NULL,
        [10. QTY] [nvarchar](255) NULL,
        [1. PN] [nvarchar](255) NULL,
        [2. PN] [nvarchar](255) NULL,
        [SHIP TO] [nvarchar](255) NULL,
        [3. PN] [nvarchar](255) NULL,
        [4. PN] [nvarchar](255) NULL,
        [5. PN] [nvarchar](255) NULL,
        [6. PN] [nvarchar](255) NULL,
        [7. PN] [nvarchar](255) NULL,
        [8. PN] [nvarchar](255) NULL,
        [9. PN] [nvarchar](255) NULL,
        [10. PN] [nvarchar](255) NULL,
        [1. PO#] [nvarchar](255) NULL,
        [2. PO#] [nvarchar](255) NULL,
        [3. PO#] [nvarchar](255) NULL,
        [4. PO#] [nvarchar](255) NULL,
        [5. PO#] [nvarchar](255) NULL,
        [6. PO#] [nvarchar](255) NULL,
        [7. PO#] [nvarchar](255) NULL,
        [8. PO#] [nvarchar](255) NULL,
        [9. PO#] [nvarchar](255) NULL,
        [10. PO#] [nvarchar](255) NULL,
        [SHIP-TO ADDRESS] [nvarchar](255) NULL,
            [6. CUSTOMER] [nvarchar](255) NULL,
        [1. CUST PN] [nvarchar](275) NULL,
        [2. CUST PN] [nvarchar](275) NULL,
        [3. CUST PN] [nvarchar](275) NULL,
        [4. CUST PN] [nvarchar](275) NULL,
        [5. CUST PN] [nvarchar](275) NULL,
        [6. CUST PN] [nvarchar](275) NULL,
        [7. CUST PN] [nvarchar](275) NULL,
        [8. CUST PN] [nvarchar](275) NULL,
        [9. CUST PN] [nvarchar](275) NULL,
        [10. CUST PN] [nvarchar](275) NULL,
        [SHIP TO ADDRESS] [nvarchar](398) NULL,

     CONSTRAINT [PK_PACKING SLIPS] PRIMARY KEY CLUSTERED 
    (
        [AUTO PACK SLIP #] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

//source

--VARIABLES
DECLARE @workorders AS TABLE
(
    [PO] NVARCHAR(10),
    [CUSTOMER] NVARCHAR(200),
    [CUST PN] INT,
    [PN] INT,
    [DESC] NVARCHAR(500),
    [QTY] INT,
    [SHIP DATE] DATE
);

--COUNTER
DECLARE @CUNTER INT;
DECLARE @MAX_CUNTER INT;


--CREATE TEST DATA
INSERT @workorders
(
    [PO],
    CUSTOMER,
    [CUST PN],
    PN,
    [DESC],
    QTY,
    [SHIP DATE]
)
VALUES
('001', N'xxx', 111, 100200, N'description1', 24, '2017-06-27'),
('002', N'xxx', 112, 100200, N'description2', 24, '2017-06-27'),
('003', N'xxx', 113, 100200, N'description3', 24, '2017-06-28'),
('004', N'xxx', 114, 100200, N'description4', 24, '2017-06-25'),
('005', N'aaa', 115, 100200, N'description5', 24, '2017-06-27'),
('006', N'aaa', 116, 100200, N'description6', 24, '2017-06-28'),
('007', N'aaa', 117, 100200, N'description7', 24, '2017-06-28'),
('008', N'ccc', 118, 100200, N'description8', 24, '2017-06-27'),
('009', N'xxx', 119, 100200, N'description9', 24, '2017-06-27');

--INSERT INTO @workorders_W_GroupingID
SELECT ROW_NUMBER() OVER (PARTITION BY S.CUSTOMER, S.[SHIP DATE] ORDER BY S.[PO]) AS groupingID,
       S.*
INTO #workorders_W_GroupingID
FROM @workorders S;

SELECT * FROM #workorders_W_GroupingID;--DISPLAY

--INITIALIZE COUNTER VARIABLES
SET @MAX_CUNTER =
(
    SELECT MAX(groupingID) + 1 FROM #workorders_W_GroupingID
);
SET @CUNTER = 1;

DECLARE @query_Select NVARCHAR(MAX),
        @query_From NVARCHAR(MAX),
        @query_Into NVARCHAR(MAX),
        @query_OrderBy NVARCHAR(MAX),
        @query NVARCHAR(MAX);

SET @query_Select = N'SELECT ';
----PO----
WHILE (@CUNTER < @MAX_CUNTER)
BEGIN
    --ADD COLUMN
    SET @query_Select
        = @query_Select
          + (' CASE WHEN temp.groupingID = 1 THEN (SELECT PO FROM #workorders_W_GroupingID WHERE  customer = temp.customer AND [ship date] = temp.[ship date] AND GroupingID = '
          + CONVERT(NVARCHAR(100), @CUNTER) + ') END AS ' + N'PO' + CONVERT(NVARCHAR(100), @CUNTER) + ',');

    --INCREASE CUNTER
    SET @CUNTER = @CUNTER + 1;
END;
--RESET CUNTER
SET @CUNTER = 1;

----CUSTOMER----
SET @query_Select = @query_Select + (' CASE WHEN temp.groupingID = 1 THEN temp.CUSTOMER END AS' + N' customer, ');

----CUST PN----
WHILE (@CUNTER < @MAX_CUNTER)
BEGIN
    --ADD COLUMN
    SET @query_Select
        = @query_Select
          + (' CASE WHEN temp.groupingID = 1 THEN (SELECT [CUST PN] FROM #workorders_W_GroupingID WHERE  customer = temp.customer AND [ship date] = temp.[ship date] AND GroupingID = '
          + CONVERT(NVARCHAR(100), @CUNTER) + ') END AS ' + N'[CUST PN' + CONVERT(NVARCHAR(100), @CUNTER) + '],');

    --INCREASE CUNTER
    SET @CUNTER = @CUNTER + 1;
END;
--RESET CUNTER
SET @CUNTER = 1;

----PN----
WHILE (@CUNTER < @MAX_CUNTER)
BEGIN
    --ADD COLUMN
    SET @query_Select
        = @query_Select
          + (' CASE WHEN temp.groupingID = 1 THEN (SELECT PN FROM #workorders_W_GroupingID WHERE  customer = temp.customer AND [ship date] = temp.[ship date] AND GroupingID = '
          + CONVERT(NVARCHAR(100), @CUNTER) + ') END AS ' + N'PN' + CONVERT(NVARCHAR(100), @CUNTER) + ',');

    --INCREASE CUNTER
    SET @CUNTER = @CUNTER + 1;
END;
--RESET CUNTER
SET @CUNTER = 1;

----DESC----
WHILE (@CUNTER < @MAX_CUNTER)
BEGIN
    --ADD COLUMN
    SET @query_Select
        = @query_Select
          + (' CASE WHEN temp.groupingID = 1 THEN (SELECT [DESC] FROM #workorders_W_GroupingID WHERE  customer = temp.customer AND [ship date] = temp.[ship date] AND GroupingID = '
          + CONVERT(NVARCHAR(100), @CUNTER) + ') END AS ' + N'DESC' + CONVERT(NVARCHAR(100), @CUNTER) + ',');

    --INCREASE CUNTER
    SET @CUNTER = @CUNTER + 1;
END;
--RESET CUNTER
SET @CUNTER = 1;

----QTY----
WHILE (@CUNTER < @MAX_CUNTER)
BEGIN
    --ADD COLUMN
    SET @query_Select
        = @query_Select
          + (' CASE WHEN temp.groupingID = 1 THEN (SELECT QTY FROM #workorders_W_GroupingID WHERE  customer = temp.customer AND [ship date] = temp.[ship date] AND GroupingID = '
          + CONVERT(NVARCHAR(100), @CUNTER) + ') END AS ' + N'QTY' + CONVERT(NVARCHAR(100), @CUNTER) + ',');

    --INCREASE CUNTER
    SET @CUNTER = @CUNTER + 1;
END;
--RESET CUNTER
SET @CUNTER = 1;

----SHIP DATE----
SET @query_Select = @query_Select + (' CASE WHEN temp.groupingID = 1 THEN temp.[SHIP DATE] END AS' + N'[ship date] ');

SET @query_From = N'FROM #workorders_W_GroupingID AS temp  ';
SET @query_Into = N' INTO ##RESULTS_2017_06_28';
SET @query_OrderBy = N'Order by BY CUSTOMER, [SHIP DATE] ';

SET @query = @query_Select + ' ' + @query_Into + ' ' + @query_From; --+ -- ' ' + @query_GroupBy


 execute (@query);

SELECT * 
FROM ##RESULTS_2017_06_28
WHERE CUSTOMER IS NOT NULL

DROP TABLE #workorders_W_GroupingID, ##RESULTS_2017_06_28;

data before and after transpose

You should transpose by using PIVOT or UNPIVOT rather than doing it manually in code. — Dai
– Dai, Commented Jun 30, 2017 at 22:27
@Dai tried. how can i do that using Customer and ship date ? and extract data from multiple row and display in a single row of multiple column — user8066749
– user8066749, Commented Jun 30, 2017 at 22:29

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I am going to suggest an alternative. The approach you have currently would fail if any customers have more purchase orders than you have allowed for. You could attempt to use a "dynamic pivot" instead (and there are many example of these) but you probably also want a fixed number of columns (perhaps you are preparing a report?)

Maybe comma separated strings could be used as an alternative approach? Where you get variable data in a fixed number of columns.

SELECT W.customer 
       , CA1.po_string
       , CA2.pn_string
       , CA3.qty_string
       , Max(W.[ship date]) max_ship_dt
FROM   @workorders W 
       CROSS APPLY (SELECT Stuff((SELECT ',' + W2.po 
                                  FROM   @workorders W2 
                                  WHERE  W2.customer = W.customer 
                                  ORDER BY W2.po, W2.[CUST PN]
                                  FOR xml path('')), 1, 1, '') AS po_string) CA1 
       CROSS APPLY (SELECT Stuff((SELECT ',' + cast(W2.[CUST PN] as nvarchar)
                                  FROM   @workorders W2 
                                  WHERE  W2.customer = W.customer 
                                  ORDER BY W2.po, W2.[CUST PN]
                                  FOR xml path('')), 1, 1, '') AS pn_string) CA2 
       CROSS APPLY (SELECT Stuff((SELECT ',' + cast(W2.qty as nvarchar)
                                  FROM   @workorders W2 
                                  WHERE  W2.customer = W.customer 
                                  ORDER BY W2.po, W2.[CUST PN]
                                  FOR xml path('')), 1, 1, '') AS qty_string) CA3 
GROUP  BY W.customer 
       , CA1.po_string
       , CA2.pn_string
       , CA3.qty_string
ORDER  BY W.customer 
;

customer | po_string           | pn_string           | qty_string     | max_ship_dt        
:------- | :------------------ | :------------------ | :------------- | :------------------
aaa      | 005,006,007         | 115,116,117         | 25,26,27       | 28/06/2017 00:00:00
ccc      | 008                 | 118                 | 28             | 27/06/2017 00:00:00
xxx      | 001,002,003,004,009 | 111,112,113,114,119 | 21,22,23,24,29 | 28/06/2017 00:00:00

dbfiddle here

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 1, 2017 at 3:40

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8066749 Over a year ago

is there any ways to create 10 fields. pn_string fields as pn1,pn2,pn3...pn10. same for others except customer and ship date. i would like to see for example customer xxx will have po1=001,po2=002,po3=003,po4=004,po5=009, po6=NULL, po7=null,po8=null, po9=null,po10=null, same for others except customer and shipdate. Thank you

Paul Maxwell Over a year ago

Just add more cross apply blocks, add one for each additional column of output. Then adjust the column to use for the data to be concatenated.

Collectives™ on Stack Overflow

How can i transpose multiple row and insert record into another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related