0

I have a table like below, there are two address primary and secondary (some EnId has 3 address):

enter image description here

I need to convert it into a row like below:

enter image description here

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'[email protected]', 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'[email protected]', N'emailId')
INSERT [dbo].[Alldata] ([id], [EnId], [value], [field_name]) VALUES (359710, 50052, N'false', N'primaryAddress');
Improve this question
9
  • Is it the order of records that defines how addresses clump together? What if concurrent insertions happen and you end up with mixed records? Ideally program doing the inserting should be adjusted so eg ENID is unique for every address or there is another column that is unique per address (per Enid) Commented Mar 22, 2021 at 5:33
  • (Isn't random data, it's just mixed up because you have to use some aggregate, so you've used MAX field but your ENID has two same fields so the resulting address is made only of the max values per field. You'd get the same from SELECT MAX(value) FROM t WHERE field = 'city' GROUP BY Enid - it's what the pivot is doing. It wants only one Enid at the end so it's grouping and picking the max value because you told it to/that's how it works - so the max city comes from the primary address but the max country comes from the secondary address) Commented Mar 22, 2021 at 5:42
  • 1
    Please don't use images for data, use tabular text or DDL+DML for sample test data. Commented Mar 22, 2021 at 5:53
  • 1
    If this is your permanent table, you should consider redesign it. It is a night-mere to query. Commented Mar 22, 2021 at 6:07
  • 2
    @squirrelt I'm guessing that because the data is a screenshot, that's how it is in the db. I can't figure out why people go to the extra effort of taking a screenshot and putting it into a question when it's easier to copy and paste text, but I'd be amazed if they took a screenshot then went to the extra effort of editing the pixels of it! :) Commented Mar 22, 2021 at 6:16

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yes, It's working. Thank you for helping and make my day. You are amazing.
2

This is a headache, because as I've mentioned in the comments you're relying on the order of rows to differentiate addresses. We'll first need some way to have a column that is the same for all the rows of an address but different for each address. Perhaps this:

SELECT *, 
  ENID * 100 +
  SUM(CASE WHEN field_name = 'companyAddress' THEN 1 ELSE 0 END) OVER (PARTITION BY Enid ORDER BY id) as Enid_addr
FROM #alldata

You should now see a enid_addr column that is like 5005201 for the first address, 5005202 for the second, and you can pivot on that

How it works is:

  • we use a case when to look at the field name and give a 1 if it's the company address row (the first row written for any particular address), otherwise a 0, so you'd end up with a column that was all 1 and 0, only ever a 1 for a company address
  • then we SUM OVER this, and by default a sum over sums up every row between the start (the same Enid with the lowest Id) and the current row. In effect it means that all the rows with the first address get 1,1,1,1, because for the first row it's 1, the second row it's 1+0, the third row is 1+0+0 and so on. and then when you hit the second occurrence of company address, is suddenly 2 (1+0+0..+1) then it's 2 for all the next rows until you hit another company address, then it's 3
  • if we take the Enid and multiply it by 100 and then add this counter it means that now the Enid is unique per address, the first N digits of it being the old Enid and then the last two giving the address number. If you ever have more than 99 addresses per person, multiply by a thousand instead etc

Now you have that data you can pivot onthis new Enid and your stuff doesn't get mixed up any more

To fit it into your query, in the middle where you have SELECT Enid, value, fieldname Replace the Enid with (ENID * 100) + SUM(CASE WHEN field_name = 'companyAddress' THEN 1 ELSE 0 END) OVER (PARTITION BY Enid ORDER BY id) as Enid

Note that you won't get exactly your desired result out of this: it'll look like 5005202 rather than 50053. If you want 50053, and you're absolutely sure you don't have another 50053 somewhere else in the table that will cause a collision, you can adjust the logic to remove the *100, and put a -1 on the end of the SUM() OVER() so you're adding 0 for the first address, 1 for the second etc

However I implore you to do as I and Squirrel asked inthe comments and. redesign the program that inserts into the table. Unless you've coded it so there is no possible way that insert of a primary address will get mixed up with insert of secondary address, you can't rely on order of rows. The program doing the inserting can easily make Enid+another column that is unique per address; just make the other column a counter int that starts at 1 and tricks up per address saved or if different machines will simultaneously write the primary and secondary addresses, a guid on each machine will be distinct

Improve this answer

1 Comment

I understand. Thank you for helping. (you help me the second time)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.