1

I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here is what the source data looks like:

    CREATE TABLE [tmp].[initial_data](
    [username] [varchar](25) NULL,
    [content] [varchar](4) NOT NULL,
    [ITEM_UIN] [varchar](20) NOT NULL,
    [SESSION_NUM] [varchar](2) NOT NULL,
    [ORDER] [int] NULL,
    [CLASS] [varchar](20) NOT NULL,
    [TYPE] [varchar](50) NULL,
    [RESPONSE] [varchar](500) NULL,
    [SCORE_PATTERN] [varchar](500) NULL,
    [ITEM_SCORE] [decimal](8, 2) NULL
) ON [PRIMARY]

Here is a sample of the data for the source data:

    INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'001174058', N'2520', N'MS23026', N'1', 2, N'OP', N'Choice 1 of n', N'C', N'1', CAST(1.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'001548698', N'2520', N'MS23026', N'1', 2, N'OP', N'Choice 1 of n', N'A', N'0', CAST(0.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'000997680', N'2520', N'MS23026', N'1', 2, N'OP', N'Choice 1 of n', N'A', N'0', CAST(0.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'001174058', N'2520', N'MSA1135', N'1', 1, N'OP', N'Choice 1 of n', N'B', N'0', CAST(0.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'001548698', N'2520', N'MSA1135', N'1', 1, N'OP', N'Choice 1 of n', N'C', N'0', CAST(0.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'000997680', N'2520', N'MSA1135', N'1', 1, N'OP', N'Choice 1 of n', N'A', N'1', CAST(1.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'001174058', N'2520', N'MSA1400', N'1', 3, N'OP', N'Inline Choice', N'C:A', N'0:1', CAST(0.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'001548698', N'2520', N'MSA1400', N'1', 3, N'OP', N'Inline Choice', N'B:A', N'1:1', CAST(1.00 AS Decimal(8, 2)))
GO
INSERT [tmp].[initial_data] ([username], [content], [ITEM_UIN], [SESSION_NUM], [ORDER], [CLASS], [TYPE], [RESPONSE], [SCORE_PATTERN], [ITEM_SCORE]) VALUES (N'000997680', N'2520', N'MSA1400', N'1', 3, N'OP', N'Inline Choice', N'A:B', N'0:0', CAST(0.00 AS Decimal(8, 2)))
GO

I want to pivot the already pivoted data so that each row has the username, and content, and the rest of the columns become groups of columns in the final output with each column in the group using the value of [order] as an _{value} at the end of each column name.

This is what I would like the final data set to look like:

CREATE TABLE [tmp].[finalpvt](  
    [username] [varchar](7) NOT NULL,
    [content] [varchar](4) NOT NULL,
    [ITEM_UIN_1] [varchar](7) NOT NULL,
    [SESSION_NUM_1] [varchar](1) NOT NULL,
    [ORDER_1] [varchar](1) NOT NULL,
    [CLASS_1] [varchar](2) NOT NULL,
    [TYPE_1] [varchar](13) NOT NULL,
    [RESPONSE_1] [varchar](1) NOT NULL,
    [SCORE_PATTERN_1] [varchar](1) NOT NULL,
    [ITEM_SCORE_1] [varchar](1) NOT NULL,
    [ITEM_UIN_2] [varchar](7) NOT NULL,
    [SESSION_NUM_2] [varchar](1) NOT NULL,
    [ORDER_2] [varchar](1) NOT NULL,
    [CLASS_2] [varchar](2) NOT NULL,
    [TYPE_2] [varchar](13) NOT NULL,
    [RESPONSE_2] [varchar](1) NOT NULL,
    [SCORE_PATTERN_2] [varchar](1) NOT NULL,
    [ITEM_SCORE_2] [varchar](1) NOT NULL,
    [ITEM_UIN_3] [varchar](7) NOT NULL,
    [SESSION_NUM_3] [varchar](1) NOT NULL,
    [ORDER_3] [varchar](1) NOT NULL,
    [CLASS_3] [varchar](2) NOT NULL,
    [TYPE_3] [varchar](13) NOT NULL,
    [RESPONSE_3] [varchar](3) NOT NULL,
    [SCORE_PATTERN_3] [varchar](3) NOT NULL,
    [ITEM_SCORE_3] [varchar](1) NOT NULL
) ON [PRIMARY]
GO

Here is the data for that table for the final result:

INSERT [tmp].[finalpvt] ([username], [content], [ITEM_UIN_1], [SESSION_NUM_1], [ORDER_1], [CLASS_1], [TYPE_1], [RESPONSE_1], [SCORE_PATTERN_1], [ITEM_SCORE_1], [ITEM_UIN_2], [SESSION_NUM_2], [ORDER_2], [CLASS_2], [TYPE_2], [RESPONSE_2], [SCORE_PATTERN_2], [ITEM_SCORE_2], [ITEM_UIN_3], [SESSION_NUM_3], [ORDER_3], [CLASS_3], [TYPE_3], [RESPONSE_3], [SCORE_PATTERN_3], [ITEM_SCORE_3]) VALUES (N'1569945', N'2520', N'MSA1135', N'1', N'1', N'OP', N'Choice 1 of n', N'B', N'0', N'0', N'MS23026', N'1', N'2', N'OP', N'Choice 1 of n', N'C', N'1', N'1', N'MSA1400', N'1', N'3', N'OP', N'Inline Choice', N'C:A', N'0:1', N'0')
GO
INSERT [tmp].[finalpvt] ([username], [content], [ITEM_UIN_1], [SESSION_NUM_1], [ORDER_1], [CLASS_1], [TYPE_1], [RESPONSE_1], [SCORE_PATTERN_1], [ITEM_SCORE_1], [ITEM_UIN_2], [SESSION_NUM_2], [ORDER_2], [CLASS_2], [TYPE_2], [RESPONSE_2], [SCORE_PATTERN_2], [ITEM_SCORE_2], [ITEM_UIN_3], [SESSION_NUM_3], [ORDER_3], [CLASS_3], [TYPE_3], [RESPONSE_3], [SCORE_PATTERN_3], [ITEM_SCORE_3]) VALUES (N'1569946', N'2520', N'MSA1135', N'1', N'1', N'OP', N'Choice 1 of n', N'C', N'0', N'0', N'MS23026', N'1', N'2', N'OP', N'Choice 1 of n', N'A', N'0', N'0', N'MSA1400', N'1', N'3', N'OP', N'Inline Choice', N'B:A', N'1:1', N'1')
GO
INSERT [tmp].[finalpvt] ([username], [content], [ITEM_UIN_1], [SESSION_NUM_1], [ORDER_1], [CLASS_1], [TYPE_1], [RESPONSE_1], [SCORE_PATTERN_1], [ITEM_SCORE_1], [ITEM_UIN_2], [SESSION_NUM_2], [ORDER_2], [CLASS_2], [TYPE_2], [RESPONSE_2], [SCORE_PATTERN_2], [ITEM_SCORE_2], [ITEM_UIN_3], [SESSION_NUM_3], [ORDER_3], [CLASS_3], [TYPE_3], [RESPONSE_3], [SCORE_PATTERN_3], [ITEM_SCORE_3]) VALUES (N'1569947', N'2520', N'MSA1135', N'1', N'1', N'OP', N'Choice 1 of n', N'A', N'1', N'1', N'MS23026', N'1', N'2', N'OP', N'Choice 1 of n', N'A', N'0', N'0', N'MSA1400', N'1', N'3', N'OP', N'Inline Choice', N'A:B', N'0:0', N'0')
GO

Each username, content, combo with always have the same number of [order] values. The source will always have the same columns to pivot. However I have multiple different content areas that have different values in the [order] column.

This sample content '2520' has [order] column values 1-67 for each username and content. But I have some as low as 1-45, and some as high as 1-80 so I would like to have the number of repeating column groups be dynamic.

I know how to do this with everything hardcoded using a (max) function and case statements for each [order] value, but I would like to have flexible or overloadable code to work for each different instances that I come across.

Is this possible? Are there any examples or functions out there that do this? I have tried the PIVOT operator but do not know how to get it to be dynamic. I am using Microsoft SQL Server 2017. Thanks in advance for any assistance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.