How do I convert columns to rows

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.

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');

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

dbfillde demo

Advertisement

Answer