September 30, 2016 at 8:43 am
Hi All,
Please i have this data (pasted below), i want to use pivot table to arrange it, but have the challenge of splitting the Question3 into five separate columns based on the data belonging to Question3 and Questions4, 5 and 6 to follow same format, I tried a query which is not giving me what i want.
Data:
CREATE TABLE [dbo].[TestTable1](
[PKID] [bigint] IDENTITY(1,1) NOT NULL,
[Identifier] [nvarchar](550) NULL,
[Data] [nvarchar](max) NULL,
[DateUpdate] [datetime] NULL,
[Period] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TestTable1] ON
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6516, N'Name', N'Eric Okhiria', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6517, N'CompanyName', N'Diamond Bank Plc', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6518, N'CompanyAddress', N'NBA House, Adeola Hopewell Street', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6519, N'Phone', N'07057024142', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6520, N'Email', N'eokhiria@diamondbank.com', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6521, N'Gender', N'Male', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6522, N'BusinessCategory', N'Corporate', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6523, N'Location', N'Lagos', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6524, N'Sector', N'Banking', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6525, N'QuestionNo1', N'4:Network and Telecoms Engineer', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6526, N'QuestionNo2', N'2', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6530, N'QuestionNo3', N'Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6531, N'QuestionNo3', N'Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6532, N'QuestionNo3', N'Others Specify...', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6533, N'QuestionNo4', N'eStream Networks:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6534, N'QuestionNo4', N'IPNX:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6535, N'QuestionNo4', N'Others specify:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6539, N'QuestionNo5', N'Metro Wireless:eStream:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6541, N'QuestionNo5', N'Metro Wireless:Others:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6542, N'QuestionNo6', N'Metro Wireless:eStream:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6543, N'QuestionNo6', N'Metro Wireless:IPNX:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6544, N'QuestionNo6', N'Metro Wireless:Others:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6545, N'QuestionNo4', N'Airtel [Enterprise]:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6546, N'QuestionNo4', N'Glo [Enterprise]:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6547, N'QuestionNo4', N'ICSL:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6548, N'QuestionNo4', N'IPNX:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6549, N'QuestionNo4', N'MainOne:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6550, N'QuestionNo4', N'MTN [Enterprise]:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6551, N'QuestionNo4', N'Estisalat:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6552, N'QuestionNo4', N'Swift Networks:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6554, N'QuestionNo4', N'21st Century:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6555, N'QuestionNo4', N'Internet Solutions:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6558, N'QuestionNo5', N'Metro Fibre:ICSL:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6560, N'QuestionNo5', N'Metro Fibre:MainOne:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6562, N'QuestionNo5', N'Metro Fibre:Estisalat:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6563, N'QuestionNo5', N'Metro Fibre:Swift:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6565, N'QuestionNo5', N'Metro Fibre:21st:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6566, N'QuestionNo5', N'Metro Fibre:Internet:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6571, N'QuestionNo4', N'Glo [Enterprise]:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6574, N'QuestionNo4', N'MainOne:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6576, N'QuestionNo4', N'Estisalat:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6577, N'QuestionNo4', N'Swift Networks:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6578, N'QuestionNo4', N'Vodacom:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6579, N'QuestionNo4', N'21st Century:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6580, N'QuestionNo4', N'Internet Solutions:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6586, N'QuestionNo4', N'Airtel [Enterprise]:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6588, N'QuestionNo4', N'ICSL:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6591, N'QuestionNo4', N'MTN [Enterprise]:Metro Wireless', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6597, N'QuestionNo5', N'Metro Wireless:Airtel:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6598, N'QuestionNo5', N'Metro Wireless:Glo:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6599, N'QuestionNo5', N'Metro Wireless:ICSL:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6600, N'QuestionNo5', N'Metro Wireless:IPNX:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6601, N'QuestionNo5', N'Metro Wireless:MainOne:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6602, N'QuestionNo5', N'Metro Wireless:MTN:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6603, N'QuestionNo5', N'Metro Wireless:Estisalat:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6604, N'QuestionNo5', N'Metro Wireless:Swift:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6605, N'QuestionNo5', N'Metro Wireless:Vodacom:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6606, N'QuestionNo5', N'Metro Wireless:21st:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6607, N'QuestionNo5', N'Metro Wireless:Internet:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6608, N'QuestionNo6', N'Metro Wireless:Airtel:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6609, N'QuestionNo6', N'Metro Wireless:Glo:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6610, N'QuestionNo6', N'Metro Wireless:ICSL:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6611, N'QuestionNo6', N'Metro Wireless:IPNX:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6612, N'QuestionNo6', N'Metro Wireless:MainOne:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6613, N'QuestionNo6', N'Metro Wireless:MTN:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6614, N'QuestionNo6', N'Metro Wireless:Estisalat:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6615, N'QuestionNo6', N'Metro Wireless:Swift:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6616, N'QuestionNo6', N'Metro Wireless:Vodacom:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6617, N'QuestionNo6', N'Metro Wireless:21st:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6618, N'QuestionNo6', N'Metro Wireless:Internet:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6626, N'QuestionNo4', N'VDT:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6627, N'QuestionNo4', N'Vodacom:Metro Fibre', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6630, N'QuestionNo5', N'Metro Fibre:Airtel:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6631, N'QuestionNo5', N'Metro Fibre:Glo:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6632, N'QuestionNo5', N'Metro Fibre:IPNX:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6634, N'QuestionNo5', N'Metro Fibre:MTN:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6637, N'QuestionNo5', N'Metro Fibre:VDT:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6638, N'QuestionNo5', N'Metro Fibre:Vodacom:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6641, N'QuestionNo6', N'Metro Fibre:Airtel:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6642, N'QuestionNo6', N'Metro Fibre:Glo:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6643, N'QuestionNo6', N'Metro Fibre:IPNX:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6644, N'QuestionNo6', N'Metro Fibre:MainOne:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6645, N'QuestionNo6', N'Metro Fibre:MTN:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6646, N'QuestionNo6', N'Metro Fibre:Estisalat:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6647, N'QuestionNo6', N'Metro Fibre:Swift:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6648, N'QuestionNo6', N'Metro Fibre:VDT:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6649, N'QuestionNo6', N'Metro Fibre:Vodacom:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6650, N'QuestionNo6', N'Metro Fibre:21st:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6651, N'QuestionNo6', N'Metro Fibre:Internet:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6652, N'QuestionNo4', N'eStream Networks:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6653, N'QuestionNo4', N'Priority Communication:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6654, N'QuestionNo4', N'VDT:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6655, N'QuestionNo4', N'Others specify:Point to Point radio', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6656, N'QuestionNo5', N'Point to Point radio:eStream:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6657, N'QuestionNo5', N'Point to Point radio:Priority:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6658, N'QuestionNo5', N'Point to Point radio:VDT:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6659, N'QuestionNo5', N'Point to Point radio:Others:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6660, N'QuestionNo6', N'Point to Point radio:eStream:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6661, N'QuestionNo6', N'Point to Point radio:Priority:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6662, N'QuestionNo6', N'Point to Point radio:VDT:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6663, N'QuestionNo6', N'Point to Point radio:Others:1', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6664, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6665, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6666, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6667, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6668, N'QuestionNo7', N'servq:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6669, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6670, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6671, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6672, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6673, N'QuestionNo7', N'servq1:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6674, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6675, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6676, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6677, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6678, N'QuestionNo7', N'servq2:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6679, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6680, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6681, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6682, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6683, N'QuestionNo7', N'servq3:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6684, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6685, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6686, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6687, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6688, N'QuestionNo7', N'servq4:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6689, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6690, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6691, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6692, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6693, N'QuestionNo7', N'servq5:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6694, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6695, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6696, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6697, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6698, N'QuestionNo7', N'servq6:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6699, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6700, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6701, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6702, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6703, N'QuestionNo7', N'servq7:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6704, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6705, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6706, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6707, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6708, N'QuestionNo8', N'servq8:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6709, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6710, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6711, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6712, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6713, N'QuestionNo8', N'servq9:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6714, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6715, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6716, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6717, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6718, N'QuestionNo8', N'servq10:4', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6719, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6720, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6721, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6722, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6723, N'QuestionNo8', N'servq11:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6724, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6725, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6726, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6727, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6728, N'QuestionNo8', N'servq12:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6729, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6730, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6731, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6732, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6733, N'QuestionNo8', N'servq13:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6734, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6735, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6736, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6737, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6738, N'QuestionNo8', N'servq14:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6739, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6740, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6741, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6742, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6743, N'QuestionNo8', N'servq15:3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6744, N'QuestionNo9', N'2', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6745, N'QuestionNo10', N'3', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6746, N'QuestionNo11', N'None', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
INSERT [dbo].[TestTable1] ([PKID], [Identifier], [Data], [DateUpdate], [Period]) VALUES (6747, N'QuestionNo12', N'Proactive monitoring of performance and not just up time as well as prompt and lasting resolution of issues.', CAST(0x0000A67B00000000 AS DateTime), N'07057024142')
GO
SET IDENTITY_INSERT [dbo].[TestTable1] OFF
GO
Query:
select Period, Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector, QuestionNo1, QuestionNo2, QuestionNo3
from
(
select Period, data, Identifier
from TestTable1
) z
pivot
(
max(data)
for Identifier in (Name, CompanyName, CompanyAddress, Phone, Email, Gender,BusinessCategory, Location, Sector, QuestionNo1, QuestionNo2, QuestionNo3 )
) piv
Thanks
Tim
September 30, 2016 at 9:05 am
What are you aiming your data to look like? We then know what you're trying to make from the data you have.
Also, it looks like your sample data contains a real persons data. I'd suggest making it fictitious if it is in fact a real person's.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 30, 2016 at 10:30 am
Thanks Thom for the reply and thanks for the advice.
The kind of result i want is in this format:
CREATE TABLE [dbo].[Testtable2](
[Period] [nvarchar](50) NULL,
[Name] [nvarchar](max) NULL,
[CompanyName] [nvarchar](max) NULL,
[CompanyAddress] [nvarchar](max) NULL,
[Phone] [nvarchar](max) NULL,
[Email] [nvarchar](max) NULL,
[Gender] [nvarchar](max) NULL,
[BusinessCategory] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL,
[Sector] [nvarchar](max) NULL,
[QuestionNo1] [nvarchar](max) NULL,
[QuestionNo2] [nvarchar](max) NULL,
[QuestionNo3a] [nvarchar](max) NULL,
[QuestionNo3b] [nvarchar](max) NULL,
[QuestionNo3c] [nvarchar](max) NULL,
[QuestionNo4a] [nvarchar](max) NULL,
[QuestionNo4b] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[Testtable2] ([Period], [Name], [CompanyName], [CompanyAddress], [Phone], [Email], [Gender], [BusinessCategory], [Location], [Sector], [QuestionNo1], [QuestionNo2], [QuestionNo3], [QuestionNo4]) VALUES (N'07057024149', N'Ericl Okhiria', N'Sure Bank Plc', N'House, Adeola Hopewell Street', N'07057024149', N'eokhi@surebank.com', N'Male', N'Corporate', N'Lagos', N'Banking', N'4:Network and Telecoms Engineer', N'2', N'Metro Fibre', N'Metro Wireless', N'Others Specify...', N'Airtel [Enterprise]:Metro Wireless', N'Airtel [Enterprise]:Metro Fibre')
Thanks
Tim
September 30, 2016 at 1:55 pm
1 - You have not provided a key that groups all of the data for a specific person. Although, from your pivot code, it appears that Period may be the key.
2 - In order to PIVOT the data, you need to have unique values for Identifier. SQL has no way to know that you want to convert QuestionNo3 into QuestionNo3a, QuestionNo3b, QuestionNo3c.
You can try to UPDATE TestTable1 and add 'a', 'b', 'c' to the end of each Identifier where there is more than one record for that identifier. Then your pivot code will work.
September 30, 2016 at 3:40 pm
This code will dynamically
- add a suffix to each duplicated QuestionNo
- build SQL to pivot the data
UPDATE dbo.TestTable1
SET Identifier = upd.Identifier
FROM dbo.TestTable1 AS orig
INNER JOIN (
SELECT
data.PKID
, Identifier = data.Identifier + sfx.suffix
FROM (
SELECT src.Identifier, src.PKID
, rn = ROW_NUMBER() OVER (PARTITION BY src.Identifier ORDER BY src.PKID)
FROM dbo.TestTable1 as src
INNER JOIN (SELECT Identifier FROM dbo.TestTable1
WHERE Identifier LIKE 'Question%'
GROUP BY Identifier
HAVING COUNT(*) > 1
) AS dup ON src.Identifier = dup.Identifier
) AS data
CROSS APPLY ( SELECT CASE WHEN data.rn <= 26 THEN CHAR(((data.rn -1) %26) + 97)
ELSE CHAR(((data.rn -1) /26) + 96) + CHAR(((data.rn -1) %26) + 97)
END
) AS sfx(suffix)
) AS upd ON orig.PKID = upd.PKID;
DECLARE @cols VARCHAR(MAX) = STUFF((SELECT ', ' + QUOTENAME(Identifier)
FROM dbo.TestTable1
ORDER BY CASE WHEN Identifier LIKE 'Question%' THEN 1 ELSE 0 END, PKID
FOR XML PATH('')
), 1, 2, '')
DECLARE @SQL VARCHAR(MAX) = '
select Period, Name, ' + @cols + '
from (select Period, data, Identifier
from dbo.TestTable1
) z
pivot ( max(data)
for Identifier in (' + @cols + ')
) piv
';
October 1, 2016 at 1:05 pm
DesNorton (9/30/2016)
1 - You have not provided a key that groups all of the data for a specific person. Although, from your pivot code, it appears that Period may be the key.2 - In order to PIVOT the data, you need to have unique values for Identifier. SQL has no way to know that you want to convert QuestionNo3 into QuestionNo3a, QuestionNo3b, QuestionNo3c.
You can try to UPDATE TestTable1 and add 'a', 'b', 'c' to the end of each Identifier where there is more than one record for that identifier. Then your pivot code will work.
Hi DesNorton,
You are absolutely right, thanks so much for the idea and the solution
I'm grateful.
Tim
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply