CODEWITHSUNDEEP
sql-servert-sql
user8066749
user8066749

Reputation: 61

How can I transpose in SQL Server?

I have the following data displayed using the select statement displayed below. but I would like to transpose based on the customer and shipby date and create a single row

 PO    CUSTOMER  CUST PN     PN     DESC           QTY    SHIP DATE  
 001     xxx      111     100200    description1    24    2017-06-27
 002     xxx      112     100201    description2    25    2017-06-27

I want to see as follows

 PO1  PO2    CUSTOMER  CUST PN1  CUST PN2    PN1    PN2      DESC 1     DESC 2             QTY1   QTY2   SHIP DATE  
 001  002     xxx      111       112      100200  100201  description1 description2         24      25   2017-06-27

ship date and customer field will be the same

Here is my SQL statement

SELECT  
    [PO #], [CUSTOMER], [CUST PN], [PN], [DESC], [QTY], [SHIP DATE]
FROM 
    (SELECT 
         [PO #], [CUSTOMER], [CUST PN], [PN], [DESC], [QTY], [SHIP DATE]
     FROM 
         WORKORDERS S
     WHERE   
         [SHIP BY] = DATEADD(day, -1, CONVERT(VARCHAR(11), GETDATE(), 106))) AS s

Upvotes: 0

Views: 143

Answers (1)

CPearson
CPearson

Reputation: 113

Hey here is my answer to your puzzle. I Found out an interesting but with SQL Server when it comes to agg columns. See comments at end of script. Also, forgive any messiness in code. Got called twice by the wife to get 'the kid'. Will try and clean up later... 

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

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



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

--COUNTER
DECLARE @CUNTER INT;
DECLARE @MAX_CUNTER INT;
SET @MAX_CUNTER =
(
    SELECT MAX(groupingID) FROM #workorders_W_GroupingID
);

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
/*ORDER OF COLUMNS IS WRONG SEE https://stackoverflow.com/questions/982286/tsql-string-concat-with-select-and-order-by-does-not-work-with-function-in-order
  Currently do not see a way to fix. Data appears to be correct.
*/

/*
SELECT * 
FROM ##RESULTS_2017_06_28
WHERE CUSTOMER IS NOT NULL
ORDER BY
    CASE 
*/
DROP TABLE #workorders_W_GroupingID, ##RESULTS_2017_06_28;

UPDATED CODE. Found some more bugs and got the ordering fixed(caused by my bugs). Will leave original code above, but BELOW is the magic ;)

--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
/*ORDER OF COLUMNS IS WRONG SEE https://stackoverflow.com/questions/982286/tsql-string-concat-with-select-and-order-by-does-not-work-with-function-in-order
  Currently do not see a way to fix. Data appears to be correct.
*/

/*
SELECT * 
FROM ##RESULTS_2017_06_28
WHERE CUSTOMER IS NOT NULL
ORDER BY
    CASE 
*/
DROP TABLE #workorders_W_GroupingID, ##RESULTS_2017_06_28;

/*Results
PO1 PO2 PO3 customer    CUST PN1    CUST PN2    CUST PN3    PN1 PN2 PN3 DESC1   DESC2   DESC3   QTY1    QTY2    QTY3    ship date
005 NULL    NULL    aaa 115 NULL    NULL    100200  NULL    NULL    description5    NULL    NULL    24  NULL    NULL    2017-06-27
006 007 NULL    aaa 116 117 NULL    100200  100200  NULL    description6    description7    NULL    24  24  NULL    2017-06-28
008 NULL    NULL    ccc 118 NULL    NULL    100200  NULL    NULL    description8    NULL    NULL    24  NULL    NULL    2017-06-27
004 NULL    NULL    xxx 114 NULL    NULL    100200  NULL    NULL    description4    NULL    NULL    24  NULL    NULL    2017-06-25
001 002 009 xxx 111 112 119 100200  100200  100200  description1    description2    description9    24  24  24  2017-06-27
003 NULL    NULL    xxx 113 NULL    NULL    100200  NULL    NULL    description3    NULL    NULL    24  NULL    NULL    2017-06-28
*/

Upvotes: 1

Related Questions