0

I have a record set:

ID  Postcode

1   S43 4AX
1   S43 4PE
1   S43 4XE
1   S43 4XN
10  S43 4AB
10  S43 4AD
10  S43 4AG
10  S43 4AH
10  S43 4AJ
10  S43 4AL

From which I want to select out:

ID    PostcodeList

1     S43 4AX, S43 4PE, S43 4XE, S43 4XN
10     S43 4AB,S43 4AD,S43 4AG,S43 4AH,S43 4AJ,S43 4AL

Is there a simple way to do this?

kmoe
  • 1,963
  • 2
  • 15
  • 27
Paul Holmes
  • 43
  • 1
  • 6
  • 1
    What RDBMS are you using? MySQL? Access? Teradata? Sql Server? – JNevill Oct 22 '14 at 15:41
  • 1
    Why don't you do a `SELECT ID,PostCode FROM TableName WHERE id=1 OR id=10` and then stitch them together in the server code using implode/String.Join etc (php/java/c#) or even front-end like javascript if it is not that many rows ? – Michail Michailidis Oct 22 '14 at 15:43
  • 1
    If you're using MySQL, see `GROUP_CONCAT()`. There are similar mechanisms for other databases. – Barmar Oct 22 '14 at 15:44
  • Check this for MS SQL SERVER http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – Michail Michailidis Oct 22 '14 at 15:45
  • 2
    This is a presentation issue. This is not a job for SQL to handle - leave it to your front-end report/webpage/form/etc. – gvee Oct 22 '14 at 15:46
  • in Postgresql you can use: `string_agg(field, delimiter)` – Jorge Campos Oct 22 '14 at 15:47
  • P.S. because people love points, they're going to tell you various methods to solve your problem. However they are almost exclusively bodges/workarounds to a problem that should simply be solved in another language/technology. Just because it can be done, doesn't mean it should. – gvee Oct 22 '14 at 15:48
  • @gvee we'll probably disagree at this point, but every major RDBMS has the ability to concatenate results in a field level group by. I would rely on the RDBMS and its often very well refined execution path to perform this rather than writing code on the front end to iterate through a recordset clumsily stitching together records. One pass at the data versus two means better consolidated resources, less confusion for the next person maintaining your app, and less chance for error. – JNevill Oct 22 '14 at 15:53
  • Appologies should have said Its MS SQL 2008 R2 – Paul Holmes Oct 22 '14 at 15:58
  • This is required in Tsql as the next step is to mail out the list of postcodes, using the sql server – Paul Holmes Oct 22 '14 at 15:59
  • Read [Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/), it goes over all the available options and discusses pros and cons. – Remus Rusanu Oct 23 '14 at 07:37

1 Answers1

0

I used the code sugested by Michail Michailidis to write this

Select Distinct a.id, CAST((
          SELECT  T.postcode+','
          FROM mtable T
          WHERE A.id = T.id
          FOR XML PATH(''))as varchar(max)) as pclist
FROM mtable a
Paul Holmes
  • 43
  • 1
  • 6