Results of a query for order numbers as rows instead of columns, grouped by NTILE(5).

Question

question

billmarmc asked Jun 23, '20

Results of a query for order numbers as rows instead of columns, grouped by NTILE(5).

I am attempting to pull the results of a query for order numbers as rows and not columns, with a grouping orders by using NTILE(5). Pivot has been tried to no avail. Query as follows:

SELECT 
NTILE(5) OVER(ORDER BY oehdrhst_sql.ord_no) AS DisplayColumn, 
oehdrhst_sql.ord_no
FROM oehdrhst_sql AS oehdrhst_sql
     INNER JOIN sycdefil_sql AS sycdefil_sql ON oehdrhst_sql.ship_via_cd = sycdefil_sql.sy_code
                                                AND sycdefil_sql.cd_type = 'V'
WHERE posted_dt >= DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(day, -30, GETDATE())))
      AND oehdrhst_sql.ship_via_cd <> 14
      AND NOT(oehdrhst_sql.user_def_fld_3 IS NULL)
      AND ord_no NOT IN
(
    SELECT OrderNumber
    FROM StennerApps.dbo.ShipNotificationsSent
)

Results of direct query:

The numbers before the order number are the NTILE(5) groupings.

Desired results:

458469  458471 458473  458475  458489
458492  458494 458496  458501  458502
458503  458510 458511  458513  458514
458515  458518 458519  458520  458521
458523  458525 458526  458530  458531

sql server 2014

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-06-26T16:35:57Z

swarnava2014 answered Jun 26, '20

Please try using the below concept...if it works

Create a new output table, use that as target, source table should be your query... then pivot and merge

Merge [new_taget_table] as target using ( SELECT DisplayColumn, ord_no FROM ( SELECT NTILE(5) OVER(ORDER BY oehdrhst_sql.ord_no) AS DisplayColumn, oehdrhst_sql.ord_no as ord_no FROM oehdrhst_sql AS oehdrhst_sql INNER JOIN sycdefil_sql AS sycdefil_sql ON oehdrhst_sql.ship_via_cd = sycdefil_sql.sy_code AND sycdefil_sql.cd_type = 'V' WHERE posted_dt >= DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(day, -30, GETDATE()))) AND oehdrhst_sql.ship_via_cd <> 14 AND NOT(oehdrhst_sql.user_def_fld_3 IS NULL) AND ord_no NOT IN ( SELECT OrderNumber FROM StennerApps.dbo.ShipNotificationsSent ) ) AS SourceTable PIVOT(MAX([oehdrhst_sql.ord_no]) FOR [DisplayColumn] IN(1,2, 3,4,5)) AS PivotTable) as source on target.Col1 = source.Col1 when matched then update set target.ord_no = source.ord_no when not matched by target then insert (col1,col2) values (source.col1,source.col2);

Thanks.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question