Solved

TSQL function to retrieve multiple values

Posted on 2016-08-24
6
52 Views
Last Modified: 2016-08-24
Hello,

Uh oh!  SQL guy is on vacation again!  

I'm trying to feed a "Race" field on an RDLC report.  My database has a person table with bit fields to indicate the race(s) a person belongs to.

The layout is something like this:
person_ID | name | DOB | IsAsian | IsBlack | IsNativeHawaiian | IsAlaskaNative | IsWhite

A person can belong to more than one race group.  I'm trying to write a function that would output a string of each race the person belongs to.  For example, if IsAlaskaNative='1' AND IsWhite='1' the output would be "Alaskan Native, White".

Any help you can provide will be greatly appreciated.  
Thanks!
0
Comment
Question by:ttist25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 41768849
Eyeballeth thy article T-SQL:  Normalized data to a single comma delineated string and back, which unfortunately is the easiest way to pull this off.
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 90 total points
ID: 41768850
try simply using IIF

SELECT person_id, IIF(IsAsian=1, 'Asian,', '') + IIF(IsBlack=1, 'Black,', '') + IIF(IsNativeHawaiian=1, 'Native Hawaiian,', '') + IIF(IsAlaskaNative=1, 'Alaskan Native,', '') + IIF(IsWhite=1, 'White', '') AS Race
FROM TABLE1

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41768853
SELECT STUFF(
    CASE WHEN raceBits & 1 > 0 THEN ', Asian' ELSE '' END +
    CASE WHEN raceBits & 2 > 0 THEN ', Black' ELSE '' END +    
    CASE WHEN raceBits & 4 > 0 THEN ', Alaskan Native' ELSE '' END +
    CASE WHEN raceBits & 8 > 0 THEN ', White' ELSE '' END,
    1, 2, '') AS Race
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 60 total points
ID: 41768878
Not a function, but this will work:

--The layout is something like this:
--person_ID | name | DOB | IsAsian | IsBlack | IsNativeHawaiian | IsAlaskaNative | IsWhite

create table #layout (personID int, name varchar(25), DOB char(8), IsAsian BIT, IsBlack BIT, IsNativeHawaiian BIT, IsAlaskaNative BIT, IsWhite BIT)
INSERT #layout (personID,name,DOB,IsAsian,IsBlack,IsNativeHawaiian,IsAlaskaNative,IsWhite)
VALUES (1,'John Doe','07-02-71',0,0,1,0,1),
(2,'Jane Doe','07-03-71',0,1,0,0,0);

--A person can belong to more than one race group.  I'm trying to write a function that would output a string
--of each race the person belongs to.  For example, if IsAlaskaNative='1' AND IsWhite='1' the output would be
--"Alaskan Native, White".

SELECT
      PersonID,
      Name,
      DOB,
      CASE WHEN IsAsian = 1 THEN 'Asian'
             WHEN IsBlack = 1 THEN 'Black'
             WHEN IsNativeHawaiian = 1 AND IsWhite = 1 THEN  'Native Hawaiian, White'
             WHEN IsNativeHawaiian = 1 AND IsWhite = 0 THEN 'Native Hawaiian'
             WHEN IsAlaskaNative = 1 AND IsWhite = 1 THEN 'Alaskan Native, White'
             WHEN IsAlaskaNative = 1 AND IsWhite = 0 THEN 'Alaskan Native'
             WHEN IsWhite = 1 AND IsNativeHawaiian = 0 AND IsAlaskaNative = 0 THEN 'White' END [Race]
FROM
      #layout


PersonID      Name      DOB      Race
1      John Doe      07-02-71         Native Hawaiian, White
2      Jane Doe              07-03-71   Black
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 41768941
I need to adjust my code.  Sorry, I originally thought all the bits were in one int column, not separate bit columns.  The core of the code is the same:

SELECT STUFF(
    CASE WHEN isAsian = 1 THEN ', Asian' ELSE '' END +
    CASE WHEN isBlack = 1 THEN ', Black' ELSE '' END +    
    CASE WHEN isAlaskanNative = 1 THEN 'Alaskan Native' ELSE '' END +
    CASE WHEN isWhite = 1 THEN ', White' ELSE '' END,
    1, 2, '') AS Race
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 41769177
This is the craftsmanship of coding.  :)  Different ways to get the same result.

Thanks so much for all of your help!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question