SQL - How to "flatten" several very similar rows into 1

Question

Apologies if the title isn't clear - I just didn't know how to describe the issue and I really don't know SQL that well/at all.

I am working with a database used by our case management system. At places it has clearly been extended over time by the developers. I am working with Contact details (names, addresses, etc...) and they have added extra fields to deal with email addresses and to allow for home/work/mobile phone numbers etc...

The problem is that they haven't added a new field for each individual new field. They have instead added a couple of fields in 2 different tables - the first field includes the field name, the second then includes the actual data.

The first is called AddElTypeText in a table called AdditionalAddElTypes - The AddElTypeText field includes values like "Work Telephone 1", "Fax", "Home Email" etc... (There are a total of 10 different values and I can't see the developers expanding this number any time soon)

The second field is called AddElementText in a table called AdditionalAddressElements - the AddElementText then includes the actual data e.g. the phone number, email address.

For those of you who (unlike me) find it easier to look at the SQL code, it's:

SELECT
  Address.AddressLine1
  ,AdditionalAddElTypes.AddElTypeText
  ,AdditionalAddressElements.AddElementText
FROM
  Address
  INNER JOIN AdditionalAddressElements
    ON Address.AddressID = AdditionalAddressElements.AddressID
  INNER JOIN AdditionalAddElTypes
    ON AdditionalAddressElements.AddElTypeID = AdditionalAddElTypes.AddElTypeID

I can work with this, but if any contact has 2 or more "additional" elements, I get multiple rows, with most of the data being the same, but just the 2 columns of AddElTypeText and AddElementText being different.

So can anyone suggest anything to "flatten" a contact into a single row. I had in mind something like concatenating AddElTypeText and AddElementText into a single string field, ideally with a space in between AddElTypeText and AddElementText, and then a : or , separating the pairs of AddElTypeText and AddElementText.

However, I have very little idea how to achieve that, or whether an entirely different approach would be better. Any help very gratefully received!

Gary

Can you edit your post with your create table statements for each table, some sample data and the desired result? — Taryn, Jun 19 '13 at 17:48
There are other questions where strings grouping is discussed, e.g. [here](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server). In some RDBMs there are already built-in functions for this (e.g. in PostgreSQL there is `array_agg()`. — Tomas Greif, Jun 19 '13 at 17:54
http://stackoverflow.com/questions/14619186/multiple-column-values-in-a-single-row — Ken White, Jun 19 '13 at 17:57
thanks everyone - I think the biggest part of my problem was how to describe it, so couldn't search. I'll look through all the links and provide more info if I need further help. Again, thanks! — user2468300, Jun 20 '13 at 08:13

score 1 · Accepted Answer · answered Jun 19 '13 at 18:05

As @twn08 said, this type of question has generally been asked before. It's generally a pain to do this kind of grouping concatenation in SQL Server, involving the use of FOR XML.

That being said, here's a SQLFiddle that (I believe) does something like what you wanted. And here's the actual query:

WITH Results AS
(
  SELECT a.*,
    t.AddElTypeText,
    aa.AddElementText
  FROM
    Address a
  INNER JOIN
    AdditionalAddressElements aa
    ON a.AddressID = aa.AddressID
  INNER JOIN
    AdditionalAddElTypes t
    ON aa.AddElTypeID = t.AddElTypeID
)
SELECT
  res.AddressID,
  STUFF((
    SELECT ', ' + AddElTypeText + ': ' + AddElementText 
    FROM Results
    WHERE (AddressID = res.AddressID) 
    FOR XML PATH (''))
  ,1,2,'') AS AdditionalElements
FROM Results res
GROUP BY res.AddressID

Thanks! To be honest, I wasn't expecting or hopiung for anyone to do the code for me, but with a couple of changes to fit into the specifics I was working with (and a DISTINCT to remove the duplicate rows :-) ), this works fantastically. The other options people have linked to will be really helpful in a few other situations where the DB structure is the same but what I want is different. So again, Thanks all — user2468300, Jun 20 '13 at 11:13

SQL - How to "flatten" several very similar rows into 1

1 Answers1