[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

SQL Server view combine fields in results

I have a table in sql server. I have 4 fields within the table that are boolean fields, either true or false. The fields are friend, relative, business and other. The view I am building will return other fields in the table, but for those 4 fields I want to show one field. For any of them that are true, return in one field the results as words. So a result set would look like this for a row that has true for each one:
ID  LName   FName  Address                   Relationship
21  Smith    John       100 Whatever Dr      Friend, Relative, Business, Other
The relationship will usually have just one or 2 of the fields set to true, but it could be anywhere from 1-4 of them. Is there a way to do this? Thanks.
0
dodgerfan
Asked:
dodgerfan
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
tim_csCommented:
Something like this.

SELECT
   YourOtherFields
   ,CASE WHEN FRIEND=1 THEN 'Friend, ' END + CASE WHEN Relative=1 THEN 'Relative, ' END +
    CASE WHEN Business=1 THEN 'Business, ' END + CASE WHEN Other=1 THEN 'Other, ' END
0
 
Scott PletcherSenior DBACommented:
SELECT ...,
    CASE WHEN friend = 1 THEN 'Friend' ELSE '' END +
    CASE WHEN relative = 1 AND friend > 0 THEN ', ' ELSE '' END +
    CASE WHEN relative = 1 THEN 'Relative' ELSE '' END +
    CASE WHEN business = 1 AND (friend + relative) > 0 THEN ', ' ELSE '' END +
    CASE WHEN business = 1 THEN 'Business' ELSE '' END +
    CASE WHEN other = 1 AND (friend + relative + business) > 0 THEN ', ' ELSE '' END +
    CASE WHEN other = 1 THEN 'Other' ELSE '' END
    AS Relationship
0
 
Scott PletcherSenior DBACommented:
IF the columns are actual BIT columns, you need to do this instead:

SELECT ...,
    CASE WHEN friend = 1 THEN 'Friend' ELSE '' END +
    CASE WHEN relative = 1 AND friend = 1 THEN ', ' ELSE '' END +
    CASE WHEN relative = 1 THEN 'Relative' ELSE '' END +
    CASE WHEN business = 1 AND friend | relative = 1 THEN ', ' ELSE '' END +
    CASE WHEN business = 1 THEN 'Business' ELSE '' END +
    CASE WHEN other = 1 AND friend | relative | business = 1 THEN ', ' ELSE '' END +
    CASE WHEN other = 1 THEN 'Other' ELSE '' END
    AS Relationship
0
 
dodgerfanAuthor Commented:
Perfect, thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now