Join columns SQL query

Question

I have three tables : enter image description here
For example here is data on database :

Is it possible to write query that provide a grid like below structure?

With writing query using simple join the result is like here :

SELECT     dbo.Contact.ContactID, dbo.Contact.ContactName, dbo.PhoneNumber.PhoneNO, dbo.PhoneType.TypeTitle
FROM         dbo.Contact INNER JOIN
                      dbo.PhoneNumber ON dbo.Contact.ContactID = dbo.PhoneNumber.ContactID AND dbo.Contact.ContactID = dbo.PhoneNumber.ContactID INNER JOIN
                      dbo.PhoneType ON dbo.PhoneNumber.PhoneType = dbo.PhoneType.PhoneTypeI

enter image description here

I don't understand what you're asking. Could you rephrase it? — , Sep 03 '11 at 08:09
Off topic: What you're asking is possible. Firstly, is this being consumed by a .NET application? I ask because it would be much cleaner to do the transposing in LINQ — Neil Fenwick, Sep 03 '11 at 08:12
@Neil Fenwick Yes that's right. Would yo please explain how do I do this using LINQ ? — Shahin, Sep 03 '11 at 08:21
possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — Martin Smith, Sep 03 '11 at 08:25

score 7 · Answer 1 · answered Sep 03 '11 at 08:10

What you are looking for is string aggregation. T-SQL doesn't natively do it (other dbs have string_agg for example). BUT you can simulate it.

Try looking for example: http://consultingblogs.emc.com/jamiethomson/archive/2009/07/16/string-aggregation-in-t-sql-amp-pl-sql.aspx

or, for the completists:

http://www.postgresonline.com/journal/archives/191-stringagg.html

if you search for SQL Server in the last link, there are three different ways to do it.

Neil Fenwick · Accepted Answer · 2011-09-03T12:54:05.783

Thanks for confirming this is going to be consumed by .NET.

The reason that I asked is that I don't think the database is the best place to go about transforming data. Not saying you never should, just that its best to use the database for what its good at: storing and retrieving data, and do the transform in the consuming code. Its a general principle to try and follow where you can - it leaves your data in a more "raw" format and therefore more likely to be reusable and consumable by other processes later.

Essentially, I've interpreted the problem is that you want to:

group by Contact and ContactType,
and then transpose & concatenate multiple rows of phone numbers.

I'm not sure what your .NET code that calls the database looks like, but you could do the following with a DataTable for example (assuming you have something like a Contact type):

List<Contact> results = (
    from dataRow in myDataTable.AsEnumerable()
    let contactData = new {
                           ContactName = dataRow.Field<string>("ContactName"),
                           PhoneType = dataRow.Field<string>("PhoneType"),
                           PhoneNumber = dataRow.Field<string>("PhoneNO")
                      }
    group contactData by new { contactData.ContactName, contactData.PhoneType } into grp
    select new Contact {
           ContactName = grp.Key.ContactName,
           PhoneType = grp.Key.PhoneType,
           PhoneNumber = grp.Aggregate( (cumulativeText, contact) => String.Format("{0}, {1}", cumulativeText, contact.PhoneNumber) )
    }
).ToList();

I didn't have IDE to hand to test, so take that as rough code. You get the principle from it though.

+1 this is the right place to do it. I would +2 if I could for providing the C# code as well. — Aaron Bertrand, Sep 03 '11 at 13:05

score 1 · Answer 3 · edited Sep 03 '11 at 13:05

1

select stuff((select distinct ','+ numbers from testtable for xml path('')),1,1,'')

Try this code

edited Sep 03 '11 at 13:05

Aaron Bertrand

272,866
37
466
490

answered Sep 03 '11 at 08:15

user909058

188
1
2
11

HABO · Answer 4 · 2011-09-03T12:26:18.893

You can use a CTE to gather up the data while rotating the phone numbers into a comma separated list. It may not be efficient, but it is a handy trick.

The following runs on AdventureWorks2008R2, though you'll need to stuff some extra data in the Person.PersonPhone table to create multiple phone numbers for a single person/number type.

; with PersonsWithTelephoneNumbersCTE (
  BusinessEntityId, FirstName, MiddleName, LastName,
  PhoneNumberTypeId, PhoneNumber, PhoneNumbers, Elements )
as (
  -- Base case: Just the person identifications with all possible phone types.
  select BusinessEntityID, FirstName, MiddleName, LastName, PhoneNumberTypeId,
    cast( '' as NVarChar(25) ), cast( '' as VarChar(MAX) ), 0
    from Person.Person as PP cross join
      Person.PhoneNumberType as PNT
  union all
  -- Add a telephone number.
  select CTE.BusinessEntityId, CTE.FirstName, CTE.MiddleName, CTE.LastName,
    PNT.PhoneNumberTypeID, PN.PhoneNumber,
    cast( CTE.PhoneNumbers + ', ' + PN.PhoneNumber as VarChar(MAX) ), CTE.Elements + 1
    from PersonsWithTelephoneNumbersCTE as CTE inner join
      Person.Person as PP on PP.BusinessEntityID = CTE.BusinessEntityId inner join
      Person.PhoneNumberType as PNT on PNT.PhoneNumberTypeID = CTE.PhoneNumberTypeId inner join
      Person.PersonPhone as PN on PN.BusinessEntityID = CTE.BusinessEntityId and PN.PhoneNumberTypeID = PNT.PhoneNumberTypeID
    where PN.PhoneNumber > CTE.PhoneNumber
  )
-- Get the person and the longest list of phone numbers for each person/phone type.
select LastName, FirstName, MiddleName,
  (select Name from Person.PhoneNumberType where PhoneNumberTypeID = Edna.PhoneNumberTypeID ) as PhoneNumberType,
  substring( PhoneNumbers, 3, len( PhoneNumbers ) - 2 ) as PhoneNumbers from (
  select BusinessEntityID, FirstName, MiddleName, LastName, PhoneNumberTypeId, PhoneNumbers,
    rank() over ( partition by BusinessEntityId, PhoneNumberTypeId order by Elements desc ) as Ranking
    from PersonsWithTelephoneNumbersCTE
  ) as Edna
  where Ranking = 1 and PhoneNumbers <> ''
  order by LastName, FirstName, MiddleName, PhoneNumberType

score 0 · Answer 5 · answered Sep 03 '11 at 08:08

0

In Mysql, there's a function GROUP_CONCAT which will do this for you. AFAIK MSSQL has no equivalent, but perhaps this blogpost will lead you closer to your goal: http://explainextended.com/2010/06/21/group_concat-in-sql-server/

answered Sep 03 '11 at 08:08

nikc.org

16,462
6
50
83

score 0 · Answer 6 · answered Sep 05 '11 at 03:52

Based sorta on Aaron's answer, try this query. It should return a result set in the form you are looking for.

    SELECT DISTINCT
    c.ContactName
    ,pt.TypeTitle
    ,(SELECT pn2.PhoneNO + ', '
        FROM dbo.PhoneNumber AS pn2
        WHERE pn.PhoneType = pn2.PhoneType
            AND pn.ContactID = pn2.ContactID
        FOR XML PATH ('')
    ) AS Numbers
FROM dbo.Contact AS c
    INNER JOIN dbo.PhoneNumber AS pn
        ON c.ContactID = pn.ContactID
    INNER JOIN dbo.PhoneType AS pt
        ON pn.PhoneType = pt.PhoneTypeID

Join columns SQL query

6 Answers6