SQLTeam.com | Weblogs | Forums

Pivot Issue


#1

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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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) ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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) ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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 ', 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)))
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(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


#2

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