Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 0 - None
- 1 - IDoNotWishToFurnish
- 2 - HispanicOrLatino
- 4 - NotHispanicOrLatino
- 8 - Mexican
- 16 - PuertoRican
- 32 - Cuban
- 64 - OtherHispanicOrLatino
- PersonID Name Ethnicity Ethnicity_Name
- 1 Joe Smith 26 HispanicOrLatino,Mexican,PuertoRican
- CREATE TABLE Application (
- ApplicationID INT,
- EthnicityID INT,
- PRIMARY KEY (ApplicationID, EthnicityID)
- );
- CREATE TABLE Ethnicity (
- EthnicityID TINYINT,
- EthnicityName VARCHAR(128),
- PRIMARY KEY (EthnicityID)
- );
- INSERT INTO Ethnicity (EthnicityID, EthnicityName)
- VALUES
- (1, 'Asian'),
- (2, 'Hispanic'),
- (4, 'Latino');
- DECLARE @InputApplicationID INT = 1;
- DECLARE @InputEthnicityIDs INT = 5; --Asian | Latino
- INSERT INTO ApplicationEthnicity (ApplicationID, EthnicityID)
- SELECT @InputApplicationID,
- E.EthnicityID
- FROM Ethnicity AS E
- WHERE E.EthnicityID & @InputEthnicityIDs = E.EthnicityID; --bitwise AND operation
- SELECT *
- FROM ApplicationEthnicity;
- DECLARE @QueryApplicationID INT = 1;
- SELECT STUFF(
- (Select ', ' + E.EthnicityName AS [text()]
- FROM dbo.ApplicationEthnicity AE
- INNER JOIN dbo.Ethnicity E
- ON AE.EthnicityID = E.EthnicityID
- WHERE AE.ApplicationID = @QueryApplicationID
- FOR XML PATH (''))
- ,1,1,'')
Add Comment
Please, Sign In to add comment