CODEWITHSUNDEEP
sqlsql-servert-sql
Shahin
Shahin

Reputation: 12843

Join columns SQL query

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

enter image description here
Is it possible to write query that provide a grid like below structure?

enter image description here
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

Upvotes: 5

Views: 609

Answers (6)

Joey
Joey

Reputation: 433

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

Upvotes: 0

user909058
user909058

Reputation: 188

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

Try this code

Upvotes: 1

Neil Fenwick
Neil Fenwick

Reputation: 6184

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:

  1. group by Contact and ContactType,
  2. 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.

Upvotes: 2

HABO
HABO

Reputation: 15816

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

Upvotes: 1

xanatos
xanatos

Reputation: 111820

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.

Upvotes: 7

nikc.org
nikc.org

Reputation: 16952

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/

Upvotes: 0

Related Questions