Solved

Complex sql query help

Posted on 2014-09-22
4
174 Views
Last Modified: 2014-09-22
Here is my situation

I have a table named Names with the following format

id int
primaryfname  varchar(250)
Primarylname   varchar(250)
akafname          varchar(250)
akalname           varchar(250)
Birthdate                 varchar(50)

Here is some sample data

ID            PrimaryFName     PrimaryLname        AkaFname          AkaLname     BirthDate    
6927      John                      Doe                           Jim                       Jones              25 Aug 1972
6927      John                      Doe                           Jim                       Doe                25 Dec 1974
7000       Jane                         Doe                                                                               10 jan   1956


I am trying to populate another table named NameList with the following format
id int
fname  varchar(250)
lname   varchar(250)
Birthdate    varchar(50)

Here is the criteria for the records should be written to the NameList Table

For a given id in the Names table, there should be

 one record for each  PrimaryFName   and   PrimaryLname    combination

if the birth date is different for the PrimaryFName   and   PrimaryLname    combination there should be a record for each different birth date

Like wise, there should be one entry for each akaFName   and   akaLname    combination associated with the PrimaryFName   and   PrimaryLname    combination.  (if there is a different birth date then another record written as well)


using the example above the namelist table should have the following records

ID            FName     Lname       BirthDate    
6927      John        Doe            25 Aug 1972
6927      John        Doe            25 Dec 1974
6927      Jim             Jones         25 Aug 1972
6927      Jim                Jones          25 Dec 1974
6927      Jim            Doe             25 Aug 1972
6927      Jim                Doe             25 Dec 1974
7000       Jane          Doe              10 jan   1956
0
Comment
Question by:johnnyg123
[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
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40336772
SELECT DISTINCT
    ID,
    CASE WHEN whichName = 'Primary' THEN primaryfname ELSE akafname END AS FName,
    CASE WHEN whichName = 'Primary' THEN primarylname ELSE akalname END AS LName,
    Birthdate
FROM names
CROSS JOIN (
    SELECT 'Primary' AS whichName UNION ALL
    SELECT 'aka'
) AS whichNames
0
 

Author Comment

by:johnnyg123
ID: 40336983
Scott,
That query is awesome ... even includes the name type!

Is there an easy way to exclude the aka name rows that have null values???
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40337040
Sure:

SELECT DISTINCT
     ID,
     CASE WHEN whichName = 'Primary' THEN primaryfname ELSE akafname END AS FName,
     CASE WHEN whichName = 'Primary' THEN primarylname ELSE akalname END AS LName,
     Birthdate
 FROM names
 INNER JOIN (
     SELECT 'Primary' AS whichName UNION ALL
     SELECT 'aka'
 ) AS whichNames ON
    whichNames.whichName = 'Primary' OR
    (akafname IS NOT NULL OR akalname IS NOT NULL)
0
 

Author Closing Comment

by:johnnyg123
ID: 40337249
Exactly what I was looking for .... Thanks Scott!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

734 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