2 Replies - 187 Views - Last Post: 26 March 2019 - 04:01 AM

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6819
  • View blog
  • Posts: 28,247
  • Joined: 12-December 12

flatten languages

Posted 26 March 2019 - 02:55 AM

This is a little tricky to explain.

I have a Contacts table.
Each Contact can have many Profiles. (It is like having customized CVs.)
Each Profile (for a Contact) can specify Languages (English, French, etc.) which are stored in BioLanguages table (linked by ProfileID).

I know this is poor design - not my decision. Besides, the languages should be stored with the contacts, not randomly generated for misc. profiles.

Anyway. When viewing contact details we will show a list of their associated languages as, again, a comma-separated string 'English,French'.

So the data could be (roughly)

ContactID 1

ProfileID 1
ContactID 1

ProfileID 2
ContactID 1

BioLanguages
-----------
ProfileID 1
LanguageID 1

ProfileID 1
LanguageID 2

ProfileID 2
LanguageID 1

ProfileID 2
LanguageID 3



The View I have so far (vwContacts) is this:

ALTER VIEW [dbo].[vwContacts]
AS
SELECT CON.contactID, CON.lastName, CON.firstName, concat(firstName,' ',lastName) as [name],
	concat(CON.email, '@stuff.com') as email, 
	CON.phone, CON.mobile,
	CON.summaryHTML, CON.education, ROL.jobTitle, CON.jobTitle as jobTitleID, 
	OFFICE.officeName as office, CON.office as officeID,

	STUFF((SELECT ', ' + CAST(Languages.languageName as varchar) FROM BioLanguages
	LEFT OUTER JOIN Languages ON Languages.languageID = BioLanguages.languageID
	WHERE BioLanguages.ProfileID = Profiles.ProfileID FOR XML PATH('')), 1, 1, '') as Languages,

	CON.headshot, CAST('' as varchar(max)) as UserName  
	FROM dbo.Contacts CON
	LEFT OUTER JOIN Roles ROL ON ROL.roleID = CON.jobTitle
	LEFT OUTER JOIN Offices OFFICE ON OFFICE.officeID = CON.office
	LEFT OUTER JOIN Profiles ON Profiles.ContactID = CON.ContactID
GO


What this will do is show all contacts but repeat a contact for each profile (obviously, because there is nothing in the view so far to "collapse" the data). So I will have

1 Bob 'English,French'
1 Bob 'English,German'
2 Terry
3 Fred


What I need to do is collapse this to a single row for each contact, but also, to remove duplicated languages. So I will end up with

1 Bob 'English,French,German'  -- with duplicate 'English' removed


How can I pursue this? I'd prefer to do it with a View rather than interrupting with some stored procedure to execute. I realise that I can use a CTE (common table expression) in a View, perhaps even a recursive one. Do you think this will do the job, and do you have a hint at what sort of expressions I would use to achieve this?

Or can a View use a table-valued function? Edit: just checked and I believe it can, although, I am not sure if it would achieve anything that a CTE couldn't.

Is This A Good Question/Topic? 0
  • +

Replies To: flatten languages

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6819
  • View blog
  • Posts: 28,247
  • Joined: 12-December 12

Re: flatten languages

Posted 26 March 2019 - 03:43 AM

I have made a little progress with the following, using two CTEs and DISTINCT to collapse repeated rows:

ALTER VIEW [dbo].[vwContacts2]
AS
WITH languagesCTE AS (
SELECT 
	CON.contactID,
	Profiles.ProfileID,
	STUFF((SELECT ',' + CAST(Languages.languageName as varchar) FROM BioLanguages
	LEFT OUTER JOIN Languages ON Languages.languageID = BioLanguages.languageID
	WHERE BioLanguages.ProfileID = Profiles.ProfileID FOR XML PATH('')), 1, 1, '') as Languages
	FROM dbo.Contacts CON
	LEFT OUTER JOIN Profiles ON Profiles.ContactID = CON.ContactID
), languagesCTE2 AS (
	SELECT languagesCTE.contactID,
	STUFF((SELECT ',' + CAST(Languages as varchar) FROM languagesCTE 
	WHERE languagesCTE.contactID = CON.contactID FOR XML PATH('')), 1, 1, '') as Languages
	FROM dbo.Contacts CON
	LEFT OUTER JOIN languagesCTE ON languagesCTE.ContactID = CON.ContactID
)
SELECT DISTINCT CON.contactID, CON.lastName, CON.firstName, concat(firstName,' ',lastName) as [name],
	concat(CON.email, '@stuff.com') as email, 
	CON.phone, CON.mobile,
	CON.summaryHTML, CON.education, ROL.jobTitle, CON.jobTitle as jobTitleID, 
	OFFICE.officeName as office, CON.office as officeID,
	CTE2.Languages,
	CON.headshot, CAST('' as varchar(max)) as UserName
	FROM Contacts CON 
	LEFT OUTER JOIN Roles ROL ON ROL.roleID = CON.jobTitle
	LEFT OUTER JOIN Offices OFFICE ON OFFICE.officeID = CON.office
	LEFT OUTER JOIN languagesCTE2 CTE2 ON CON.contactID = CTE2.contactID
GO


I now have a single row for Bob with his Languages shown as English,French,English,German,English. So my task looks to be reduced to removing the duplicates from this string.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6819
  • View blog
  • Posts: 28,247
  • Joined: 12-December 12

Re: flatten languages

Posted 26 March 2019 - 04:01 AM

Well, I have a solution using code here which uses a helper table of numbers, and splits and reassembles the string:

ALTER VIEW [dbo].[vwContacts]
AS
WITH languagesCTE AS (
SELECT 
	CON.contactID,
	Profiles.ProfileID,
	STUFF((SELECT ',' + CAST(Languages.languageName as varchar) FROM BioLanguages
	LEFT OUTER JOIN Languages ON Languages.languageID = BioLanguages.languageID
	WHERE BioLanguages.ProfileID = Profiles.ProfileID FOR XML PATH('')), 1, 1, '') as Languages
	FROM dbo.Contacts CON
	LEFT OUTER JOIN Profiles ON Profiles.ContactID = CON.ContactID
), languagesCTE2 AS (
	SELECT languagesCTE.contactID,
	STUFF((SELECT ',' + CAST(Languages as varchar) FROM languagesCTE 
	WHERE languagesCTE.contactID = CON.contactID FOR XML PATH('')), 1, 1, '') as Languages
	FROM dbo.Contacts CON
	LEFT OUTER JOIN languagesCTE ON languagesCTE.ContactID = CON.ContactID
)
SELECT DISTINCT CON.contactID, CON.lastName, CON.firstName, concat(firstName,' ',lastName) as [name],
	concat(CON.email, '@stuff.com') as email, 
	CON.phone, CON.mobile,
	CON.summaryHTML, CON.education, ROL.jobTitle, CON.jobTitle as jobTitleID, 
	OFFICE.officeName as office, CON.office as officeID,

	dbo.ReassembleString(CTE2.Languages, N',', N'Alphabetical') AS Languages,

	CON.headshot, CAST('' as varchar(max)) as UserName
	FROM Contacts CON 
	LEFT OUTER JOIN Roles ROL ON ROL.roleID = CON.jobTitle
	LEFT OUTER JOIN Offices OFFICE ON OFFICE.officeID = CON.office
	LEFT OUTER JOIN languagesCTE2 CTE2 ON CON.contactID = CTE2.contactID
GO


It is all a bit clumsy, but it works (so far).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1