CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nick Taylor
Nick Taylor

Reputation: 161

Specify Which Column Comes First SQL

I am processing a large list of church members in order to send them a letter. We want the letter to say "Dear John & Jane Smith". We will use Word to do the mail merge from an Excel sheet. The important thing is the male name has to always come first.

Each individual has their own row in the table I am using. They have a unique ID as well as a family ID. I am using that family ID to put families together on the same row. Currently I have the male name and the female name separated using MAX(CASE WHEN) in order to specify what goes where. It looks something like this:

+-----------+------------+--------------------------+
| family id | male name  | female name |  last name |
+-----------+------------+--------------------------+
|      1234 | john       | jane        |  doe       |
|      1235 | bob        | cindy       |  smith     | 
|      1236 | NULL       | susan       |  jones     |
|      1237 | jim        | NULL        |  taylor    |
+-----------+------------+--------------------------+

But I run into a problem when the family only has one member.

Here's a part of the query I have:

SELECT
fm.family_id AS 'Family ID',
MAX(CASE WHEN PB.gender like 'm' and FM.role_luid=29 THEN PB.nick_name END) 
AS 'Male Name',
MAX(CASE WHEN PB.gender like 'f' and FM.role_luid=29 THEN PB.nick_name END) 
AS 'Female Name',
PB.last_name AS 'Last Name',

FROM core_family F

I was thinking that I need to combine rows using STUFF or something like that, but I'd need some way of specifying which column comes first so that the male name always comes first. Essentially, as stated above, I need the letter to read "Dear John & Jane Smith" for families with two people and "Dear John Smith" for families with one person. So I am hoping my results might look like:

+-----------+--------------+-----------+
| family id | First name   | last name |
+-----------+--------------+-----------+
|      1234 | john & jane  | doe       |
|      1235 | bob & cindy  | smith     | 
|      1236 | susan        | jones     |
|      1237 | jim          | taylor    |
+-----------+--------------+-----------+

Upvotes: 2

Views: 141

Answers (3)

BJones
BJones

Reputation: 2460

I've created some test data. This technique works with the test data.

CREATE TABLE #Temp (FamID INT, 
                      MaleName VARCHAR(20), 
                      FemaleName VARCHAR(20), 
                      LName VARCHAR(20))

INSERT #Temp
VALUES  (1234, 'John'  ,'Jane'   ,  'Doe' ),
        (1235, 'Bob'   , 'Cindy' , 'Smith'),
        (1236 , NULL  , 'Susan' , 'Jones'),
        (1237 , 'Jim'   , NULL  , 'Taylor')

Here is your query.

SELECT  FamID, 
        ISNULL(MaleName+' ','') +
          CASE WHEN MaleName IS NULL OR FemaleName IS NULL THEN '' ELSE 'and ' END+ 
        ISNULL(FemaleName,'') AS FirstName, 
    LName
FROM #Temp

Upvotes: 0

EoinS
EoinS

Reputation: 5482

You can use your intermediate table (assuming you don't have 3 names for a family id).

From the table you indicated use:

select
 id
 , coalesce(male_name+' & '+female_name,male_name, female_name)
 , last_name 
from F;

Here is an example with your data

Basically if you concatenate using + in Sql Server you will get null. So if either male or female name is NULL, you get NULL. Coalesce will move on to the next value if it sees NULL. This way you either get a pair with '&' or a single name for each family.

Upvotes: 3

swapnil mule
swapnil mule

Reputation: 1

You can use like this

SELECT                                
fm.family_id AS 'Family ID',
    MAX(CASE WHEN PB.gender like 'm' and FM.role_luid=29 THEN PB.nick_name END)  
 + '&'+      
MAX(CASE WHEN PB.gender like 'f' and FM.role_luid=29 THEN PB.nick_name END) 
AS 'First Name',
    PB.last_name AS 'Last Name',
    FROM core_family F

Upvotes: -1

Related Questions