I have a table like below, there are two address primary and secondary (some EnId has 3 address):
I need to convert it into a row like below:
I try this SQL statement, but it returns random data.
x
SELECT
EnId, companyAddress, suite,city, state, zip,
phoneNo, fax, emailId, country, primaryAddress
FROM
(SELECT
EnId, Value, field_name
FROM
#ALLdata) src
PIVOT
(MAX(Value)
FOR field_name IN (companyAddress,suite, city.state, zip, PhoneNo,
fax, emailId, country, primaryAddress)
) pvt ;
Attaching Script:
CREATE TABLE [dbo].[Alldata](
[id] [bigint],
[EnId] [bigint] ,
[value] [nvarchar](max) ,
[field_name] [nvarchar](200))
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359691, 50052, N'123', N'suite')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359692, 50052, N'18', N'country')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359693, 50052, N'3025', N'state')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359694, 50052, N'30951', N'city')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359695, 50052, N'EC2A 4EG', N'zip')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359696, 50052, N'998-997-2050', N'phoneNo')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359698, 50052, N'info@asite.com', N'emailId')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359699, 50052, N'true', N'primaryAddress')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359701, 50052, N'Test data', N'companyAddress')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359702, 50052, N'Test222', N'suite')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359703, 50052, N'108', N'country')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359704, 50052, N'85', N'state')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359705, 50052, N'5', N'city')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359706, 50052, N'', N'zip')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359707, 50052, N'562151256126', N'phoneNo')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359709, 50052, N'shfagsdhbf@gmail.com', N'emailId')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359710, 50052, N'false', N'primaryAddress');
Advertisement
Answer
assuming that you will not have missing row for each set of address, you can use row_number() over (partition by field_name order by id)
to generate a number to identify each set of address.
Once you have that, you can use conditional case with aggregrate to do the pivoting.
WITH
data AS
(
SELECT *, addr_no = row_number() over (partition by field_name order by id)
FROM dbo.Alldata
)
SELECT EnId,
MAX(CASE WHEN field_name = 'companyAddress' THEN value END) AS companyAddress,
MAX(CASE WHEN field_name = 'suite' THEN value END) AS suite,
MAX(CASE WHEN field_name = 'city' THEN value END) AS city,
MAX(CASE WHEN field_name = 'state' THEN value END) AS state,
. . . .
FROM data
GROUP BY EnId, addr_no