Solved

Complex sql query help

Posted on 2014-09-22
4
163 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
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
Exactly what I was looking for .... Thanks Scott!!!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now