Concatenating data from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatenating data from one table to another

Author

Topic

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-03 : 12:50:43

Please excuse the subject if not clear. I am new to SQL.

I have a table and a view. Let's call the Table dbo.EmpPers and the view dbo.codes

EmpPers holds personal information and Codes holds all the codes for the company, naturally.

There is a field in Emppers called 'EthnicID'. It a number say for example 1, 2, 3 ect...

'Codes' has a field called CodDesc. It's a varchar for example White, Hispanic, African American. ect...

What is the best way to match up the description with the code? Say for all Persons who have have the Hispanic Code '2', I want to add the description to it in the select statement.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-03 : 15:29:28

If your code column has a lot of other codes (unrelated to ethnicity codes), you will need to restrict the join condition below further:

select a.col1, a.col2, b.CodeDesc
from dbo.EmpPers a
left join dbo.codes b on a.EthnicID = b.CodeColumnName
where a.EthnicCode = 2

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-06 : 12:31:19

I did the left join, but the results are not what I need, so I'm probably not explaining what I need correctly. The Codes table has many different codes, but only want to pull in the ones that are ethnic descriptions. I want to match these descriptions to the EthnicID in the Emppers table. So for example, all people in EmpPers that have a EthnicID of 1, I want to pull in the CodDesc "White" from the codes table. There are 8 different EnthnicID's that I want to give each their appropriate description in the query.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-06 : 15:14:37

Post some sample data and desired results

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-06 : 15:33:55

How would you know how to map EthnicID=1 to 'White'? Is there a column in Codes that specifies the type of data for that row?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-06 : 15:49:26

Here is Codes table scripted out

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Codes](
[CodCode] [char](15) NULL,
[CodDesc] [varchar](45) NULL,
[CodDosCode] [char](15) NULL,
[CodDosTable] [varchar](50) NULL,
[CodModifyStamp] [char](12) NULL,
[CodNoteLink] [char](10) NULL,
[CodSystem] [char](1) NULL,
[CodSystemID] [char](12) NULL,
[CodTable] [varchar](50) NULL,
[UniqueID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Codes] ADD [CodCountryCode] [char](3) NOT NULL
ALTER TABLE [dbo].[Codes] ADD [RowLastChanged] [datetime] NULL
CONSTRAINT [PK_Codes] PRIMARY KEY NONCLUSTERED
(
[UniqueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Codes] ADD DEFAULT ('N') FOR [CodSystem]
GO

ALTER TABLE [dbo].[Codes] ADD DEFAULT ('ALL') FOR [CodCountryCode]
GO

ALTER TABLE [dbo].[Codes] ADD DEFAULT (getdate()) FOR [RowLastChanged]
GO

ALTER TABLE [dbo].[Codes] WITH CHECK ADD CONSTRAINT [CHK_Codes_CodSystem] CHECK (([CodSystem]='N' OR [CodSystem]='Y'))
GO

ALTER TABLE [dbo].[Codes] CHECK CONSTRAINT [CHK_Codes_CodSystem]
GO

Here is the EmpPers View

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW [dbo].[EmpPers] WITH VIEW_METADATA
AS

SELECT CONVERT(TINYINT, NULL) AS AuditAction
,NULLIF(e.AuditKey, -1) AS AuditKey
,Municipality AS EepAddressCity
,a.CountryCode AS EepAddressCountry
,County AS EepAddressCounty
,AddressEMail AS eepAddressEMail
,Latitude AS EepAddressLatitude
,e.AddressID AS eepAddressID
,Line1 AS EepAddressLine1
,Line2 AS EepAddressLine2
,Line3 AS EepAddressLine3
,Line4 AS EepAddressLine4
,Longitude AS EepAddressLongitude
,Region AS EepAddressState
,PostalCode AS EepAddressZipCode
,COBRAExport AS eepCOBRAExport
,COBRAIsActive AS eepCOBRAIsActive
,COBRAReason AS eepCOBRAReason
,COBRAStatus AS eepCOBRAStatus
,COBRAStatusDate AS eepCOBRAStatusDate
,ConsentElectronicW2 AS eepConsentElectronicW2
,ConsentElectronicW2PR AS EepConsentElectronicW2PR
,DateDeceased AS EepDateDeceased
,DateOfBirth AS EepDateOfBirth
,DateOfCOBRAEvent AS eepDateOfCOBRAEvent
,DateOfCOBRAExport AS eepDateOfCOBRAExport
,DateOfCOBRALetter AS eepDateOfCOBRALetter
,DateOfI9Expiration AS eepDateOfI9Expiration
,DatetimeChanged AS eepDatetimeChanged
,DatetimeCreated AS eepDatetimeCreated
,DisabilityType AS eepDisabilityType
,EEID AS eepEEID
,EthnicID AS eepEthnicID
,Gender AS EepGender
,HealthBloodType AS eepHealthBloodType
,HealthEyes AS eepHealthEyes
,HealthHair AS eepHealthHair
,HealthHeightFeet AS eepHealthHeightFeet
,HealthHeightInches AS eepHealthHeightInches
,HealthLastDonateDate AS eepHealthLastDonateDate
,HealthWeight AS eepHealthWeight
,HomeCoID AS eepHomeCoID
,I9AlienNumber AS eepI9AlienNumber
,I9DocA AS eepI9DocA
,I9DocB AS eepI9DocB
,I9DocC AS eepI9DocC
,I9Verified AS eepI9Verified
,I9VISAType AS eepI9VISAType
,I9WorkAuth AS eepI9WorkAuth
,IsDisabled AS eepIsDisabled
,IsMultiPayGroupEE AS eepIsMultiPayGroupEE
,NULLIF(CASE WHEN IsSmoker = 1 THEN 'Y' ELSE 'N' END, 'X') AS EepIsSmoker
,MaritalStatus AS eepMaritalStatus
,Military AS eepMilitary
,MilitaryBranchServed AS eepMilitaryBranchServed
,MilitaryEra AS eepMilitaryEra
,MilitaryIsDisabledVet AS eepMilitaryIsDisabledVet
,MilitaryIsOthEligVet AS eepMilitaryIsOthEligVet
,MilitaryIsOthEligVetBasis AS eepMilitaryIsOthEligVetBasis
,MilitaryIsActiveWartimeVet as eepMilitaryIsActiveWartimeVet
,ModifyStamp AS eepModifyStamp
,GivenName AS EepNameFirst
,FormerName AS EepNameFormer
,FamilyName AS EepNameLast
,MiddleName AS EepNameMiddle
,PreferredGivenName AS EepNamePreferred
,Prefix AS EepNamePrefix
,Suffix AS EepNameSuffix
,OldSSN AS eepOldSSN
,OriginCountry AS eepOriginCountry
,OriginLocation AS eepOriginLocation
,CountryPrefix AS EepPhoneHomeCountry
,Number AS EepPhoneHomeNumber
,Picture AS eepPicture
,SSAMagEEName AS eepSSAMagEEName
,SSN AS eepSSN
,UDField01 AS eepUDField01
,UDField02 AS eepUDField02
,UDField03 AS eepUDField03
,UDField04 AS eepUDField04
,UDField05 AS eepUDField05
,UDField06 AS eepUDField06
,UDField07 AS eepUDField07
,W2IsDeceased AS eepW2IsDeceased
,COBRANotes AS eepCOBRANotes
,SuppressSSN AS eepSuppressSSN
,AddressSMS AS eepAddressSMS
,MilitarySeparationDate AS eepMilitarySeparationDate
,NULLIF(CASE WHEN IsPrivate = 0 THEN 'N' ELSE 'Y' END, 'X') AS EepPhoneHomeIsPrivate
,SMSApprovals AS eepSMSApprovals
,SMSPayNotification AS eepSMSPayNotification
,SuppressDDA AS eepSuppressDDA
,I9VisaExpirationDate AS eepI9VisaExpirationDate
,UDField08 AS eepUDField08
,UDField09 AS eepUDField09
,UDField10 AS eepUDField10
,UDField11 AS eepUDField11
,UDField12 AS eepUDField12
,UDField13 AS eepUDField13
,UDField14 AS eepUDField14
,UserID AS eepUserID
,MilitaryIsMedalVet AS eepMilitaryIsMedalVet
,LastNameNotSameAsSSCard AS eepLastNameNotSameAsSSCard
,ChkCashingInstCode AS eepChkCashingInstCode
,AddressEMailAlternate AS eepAddressEMailAlternate
,Nationality1 AS eepNationality1
,Nationality2 AS eepNationality2
,Nationality3 AS eepNationality3,
e.PersonId as eepPersonId,
CandidateKey as eepCandidateKey
FROM iEmpPers e WITH (NOLOCK)
JOIN Person p WITH (NOLOCK) ON e.PersonID = p.PersonID
LEFT JOIN PersonNames pna WITH (NOLOCK) ON p.PrimaryNameID = pna.PersonNameID
LEFT JOIN Addresses a WITH (NOLOCK) ON e.AddressID = a.AddressID
LEFT JOIN PhoneNumbers phn WITH (NOLOCK) ON e.HomePhoneID = phn.PhoneID

I want to select all the columns from EmpPers and bring in the CodDesc from the Codes View.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-06 : 15:54:53

The view doesn't have any columns that can be linked to the Codes table. There needs to be a link somewhere. Is there another table that can link them?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-06 : 15:56:42

Please don't use NOLOCK. It's an extremely bad idea.

Also, what column in the Codes table matches the EthnicId in the view?

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-06 : 16:16:13

Thanks for the tips. Fortunate enough for me, I did not write this table or any of these objects. NOLOCK is always frowned upon from my experience This is a very large SaaS DB with over 2k tables and views. My DBA cringes because it's band-aid after band-aid in this environment.

There could be 5 more tables for all I know that you would have to join in and could take days to find the linked table(s). Is there a way to say - if code = 2, then append or concatenate it with a description within the query? Same with all the other Ethnic Codes.

Thanks for all your help and patience. It is much appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-06 : 16:18:23

Sure, use CASE: CASE WHEN EthnicID=1 THEN 'White' WHEN ... END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-06 : 17:07:34

Works like a charm. Thanks again