Pivot Issue

Vack · April 11, 2016, 6:03pm

Working with the following table and data.

GO

/****** Object: Table [dbo].[oecmthst_sql] Script Date: 04/11/2016 13:37:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[oecmthst_sql](
[ord_type] char NOT NULL,
[ord_no] char NOT NULL,
[line_seq_no] [smallint] NOT NULL,
[lvl_no] char NOT NULL,
[cmt_type] char NOT NULL,
[cmt_seq_no] [smallint] NOT NULL,
[cmt] char NULL,
[doc_type] char NULL,
[real_ord_no] char NULL,
[extra_1] char NULL,
[extra_2] char NULL,
[extra_3] char NULL,
[extra_4] char NULL,
[extra_5] char NULL,
[extra_6] char NULL,
[extra_7] char NULL,
[extra_8] char NULL,
[extra_9] char NULL,
[extra_10] [decimal](16, 6) NULL,
[extra_11] [decimal](16, 6) NULL,
[extra_12] [decimal](16, 2) NULL,
[extra_13] [decimal](16, 2) NULL,
[extra_14] [int] NULL,
[extra_15] [int] NULL,
[filler_0001] char NULL,
[ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

GO
/****** Object: Table [dbo].[oecmthst_sql] Script Date: 04/11/2016 13:58:56 ******/
INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 1, N'Shipped on: 03/16/2016 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 2, N'Tracking#: 1Z4824860349082424 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 3, N' Service: Ground INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 4, N' Total Weight: 56.4 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 5, N' Number of Packages: 1 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 6, N' Billing Option: Freight Collect INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 163419', 0, N'M', N'L', 7, N'End Shipment(s) INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 1, N'Shipped on: 03/16/2016 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 2, N'Tracking#: 1Z4824860349082424 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 3, N' Service: Ground INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 4, N' Total Weight: 56.4 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 5, N' Number of Packages: 1 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 6, N' Billing Option: Freight Collect INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 7, N'End Shipment(s) INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 8, N'Shipped on: 03/31/2016 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 9, N'Tracking#: 1Z4824860349511335 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 10, N' Service: Ground INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 11, N' Total Weight: 9.2 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 12, N' Number of Packages: 1 INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 13, N' Billing Option: Prepaid INSERT [dbo].[oecmthst_sql] ([ord_type], [ord_no], [line_seq_no], [lvl_no], [cmt_type], [cmt_seq_no], [cmt], [doc_type], [real_ord_no], [extra_1], [extra_2], [extra_3], [extra_4], [extra_5], [extra_6], [extra_7], [extra_8], [extra_9], [extra_10], [extra_11], [extra_12], [extra_13], [extra_14], [extra_15], [filler_0001], [ID]) VALUES (N'O', N' 164189', 0, N'M', N'L', 14, N'End Shipment(s) ', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140102 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140103 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140104 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140105 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140106 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140107 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(140108 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143156 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143157 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143158 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143159 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143160 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143161 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143162 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143163 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143164 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143165 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143166 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143167 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143168 AS Numeric(9, 0)))
', NULL, N' 500216', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0.000000 AS Decimal(16, 6)), CAST(0.000000 AS Decimal(16, 6)), CAST(0.00 AS Decimal(16, 2)), CAST(0.00 AS Decimal(16, 2)), NULL, NULL, NULL, CAST(143169 AS Numeric(9, 0)))

This script is only looking at 1st seven line_seq_no
I need to be able to look at the next 7 if they exist and make them a new record in this pivot.

SELECT ord_no, ltrim([1]) AS ShipDate, [2] AS Tracking, ltrim([3]) AS Service, [4] AS Weight, [5] AS Packages, [6] AS Billing, [7] AS LTL, real_ord_no
FROM (SELECT ord_no, cmt_seq_no, cmt, real_ord_no
FROM oecmthst_sql
WHERE line_seq_no = 0 AND (LEFT(ltrim(cmt), 11) = 'Shipped on:' OR
LEFT(ltrim(cmt), 10) = 'Tracking#:' OR
LEFT(ltrim(cmt), 8) = 'Service:' OR
LEFT(ltrim(cmt), 13) = 'Total Weight:' OR
LEFT(ltrim(cmt), 19) = 'Number of Packages:' OR
LEFT(ltrim(cmt), 15) = 'Billing Option:' OR
LEFT(ltrim(cmt), 3) = 'LTL')) p PIVOT (max(cmt) FOR cmt_seq_no IN ([1], [2], [3], [4], [5], [6], [7])) AS pvt

JamesK · April 11, 2016, 8:43pm

The following may not be exactly what you want, but it should be easy to modify the grouping and seq number to achieve what you want. The general idea is to provide an additional grouping and a revised sequence number on which to pivot.

SELECT  ord_no ,
        LTRIM([1]) AS ShipDate ,
        [2] AS Tracking ,
        LTRIM([3]) AS Service ,
        [4] AS Weight ,
        [5] AS Packages ,
        [6] AS Billing ,
        [7] AS LTL ,
        real_ord_no
FROM    ( SELECT    ord_no ,
                    --cmt_seq_no ,
                    cmt_seq_no/7 AS NEW_Group ,
                    cmt_seq_no%7 AS NEW_cmt_seq_no,
                    cmt ,
                    real_ord_no
          FROM      oecmthst_sql
          WHERE     line_seq_no = 0
                    AND ( LEFT(LTRIM(cmt), 11) = 'Shipped on:'
                          OR LEFT(LTRIM(cmt), 10) = 'Tracking#:'
                          OR LEFT(LTRIM(cmt), 8) = 'Service:'
                          OR LEFT(LTRIM(cmt), 13) = 'Total Weight:'
                          OR LEFT(LTRIM(cmt), 19) = 'Number of Packages:'
                          OR LEFT(LTRIM(cmt), 15) = 'Billing Option:'
                          OR LEFT(LTRIM(cmt), 3) = 'LTL'
                        )
        ) p PIVOT ( MAX(cmt) FOR NEW_cmt_seq_no IN ( [1], [2], [3], [4], [5], [6],
                                                 [7] ) ) AS pvt