0

If a person has got two addresses then I need post a letter for both the addresses otherwise post to the letter to the family address which is one.

SELECT id, firstname, lastname, address, postcode, email
From constituent

The data I have is as below:

10001,Esther,Wheels,101 Rosehill, WA1878,sue.w@hotmail.com
10001,Esther,wheels,30 miles rd, 123UDF,hws.isthe@hotmail.com
10001,Esther,Wheels,101 Rosehill, WA1878,

How to put this information in a single row showing id,firstname,lastname, address1,postcode1,address2,postcode2,email1,email2,First

Family(flag), Second family(x)? If there is no second address it should show just one address.

Please help?

Alexey S. Larionov
  • 6,555
  • 1
  • 18
  • 37
Sindhu B
  • 59
  • 8
  • What if there are more than two addresses? If you are using SQL Server, the appropriate tag is "sql-server". – Gordon Linoff Feb 08 '21 at 12:58
  • Please show the expected output for the sample data you provided. – Kurt Kline Feb 08 '21 at 13:00
  • @GordonLinoff, if there are three address then it should all the three addresses , but if not atleast two should pop up as a second family – Sindhu B Feb 08 '21 at 13:28
  • @kurtkline 10001,Esthefr,Wheels,101 Rosehill,WA1878,30 miles rd,123UDF,sue.w@hotmail.com,hws.isthe@hotmail.com,x,x. – Sindhu B Feb 08 '21 at 13:30
  • 1
    You want to do a [`PIVOT`](https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot). Because your number of columns depends on your data, you will need a "dynamic pivot" (pivot + dynamics SQL) like in [this question](https://stackoverflow.com/questions/19688697/mssql-dynamic-pivot-column-values-to-column-header). – Sander Feb 08 '21 at 14:07
  • Why do you have the same name three times? If they are the same person, there should be a `Person` table, and an `Address` table which references it – Charlieface Feb 08 '21 at 14:25
  • Doing this in SQL will probably be ugly and unmaintainable. IMHO better to pull all addresses with one query and let the code getting the addresses handle putting it into a single row. – KyleUp Feb 08 '21 at 14:31

1 Answers1

0

Please check is below solution suitable for you:

SELECT 
  id, 
  lastname,
  STRING_AGG(CONCAT(address, ',', postcode), ',') address,
  STRING_AGG(email, ',') email
FROM 
  constituent
GROUP BY id, lastname;

MSSQL fiddle

Result:

+=======+==========+============================================================+==========================================+
| id    | lastname | address                                                    | email                                    |
+=======+==========+============================================================+==========================================+
| 10001 | Wheels   | 101 Rosehill,WA1878,30 miles rd,123UDF,101 Rosehill,WA1878 | sue.w@hotmail.com,hws.isthe@hotmail.com, |
+-------+----------+------------------------------------------------------------+------------------------------------------+
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39