Geek Answers Handbook

Join SQL query columns

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

enter image description here
Is it possible to write a query that provides a grid similar to the structure below?

enter image description here
When writing a query using a simple join, the result is as follows:

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

+5
source share
6 answers

Thank you for confirming that it will be consumed by .NET.

, , , , - . , , , , : , . , , , - "" , , , .

, , , :

  • ,
  • .

, .NET, , DataTable ( - Contact):

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();

IDE , . , .

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

+1

CTE , . , .

AdventureWorks2008R2, Person.PersonPhone / .

; 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
+1

Mysql GROUP_CONCAT, . AFAIK MSSQL , , , - : http://explainextended.com/2010/06/21/group_concat-in-sql-server/

0

Type sorta on Aaron's answer, try this query. It should return the 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
0
source

More articles:


All Articles