Urgent help needed

Question

Post reply

Urgent help needed

lassell

Ten Centuries

Points: 1215
More actions
August 12, 2008 at 10:03 pm

#189318

I know this may be a really simple prob for most people on this site but I am having to pick up SSIS on the fly and need urgent help on my Project.
I have two tables I am trying to import via SSIS and one of them uses a look up table for firstname, middlename, lastname and preferredname. I am using the following queries to extract the data but I am required to have them in one table for SSIS.
SELECT
N.PersonID,
(SELECT Name WHERE nameTypeID = 7780 ) AS NameFirst,
(SELECT Name where nametypeid = 7781 )AS NameMiddle ,
(SELECT Name WHERE nameTypeID = 7783 ) AS NamePreferred ,
(SELECT Name WHERE nameTypeID = 7779 )AS NameLast
FROM
Name N
INNER JOIN Person p on p.IdentityID=PersonID
where personid = 11701 -- FOR TESTING PURPOSES
GROUP BY
N.PersonID,
NameID,
N.Name,
n.nametypeid
The query returns the correct results but is presented as follows;
11701ShirlieNULLNULLNULL
11701NULLPatriciaNULLNULL
11701NULLNULLNULLParkinson
11701NULLNULLNULL
Really I need the following;
11701Shirlie Patricia Parkinson
I do know this is simple but please help!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

j nuñez SSCrazy Points: 2175 More actions · Answer 1

Can you put your table definitions (Person and Name) and give us an example of how is your data stored in the tables?

Ken McKelvey SSCoach Points: 18843 More actions · Answer 2

Try using CASE

SELECT N.PersonID

&nbsp&nbsp&nbsp&nbsp,MAX(CASE N.nameTypeID = 7780 THEN [Name] END) AS NameFirst

&nbsp&nbsp&nbsp&nbsp,MAX(CASE N.nameTypeID = 7781 THEN [Name] END) AS NameMiddle

&nbsp&nbsp&nbsp&nbsp,MAX(CASE N.nameTypeID = 7783 THEN [Name] END) AS NamePreferred

&nbsp&nbsp&nbsp&nbsp,MAX(CASE N.nameTypeID = 7779 THEN [Name] END) AS NameLast

FROM [Name] N

GROUP BY N.PersonID

lassell Ten Centuries Points: 1215 More actions · Answer 3

Hi, many thanks for your help with this. I ended up using the following tho;

select PersonID, max(Namefirst) as NameFirst,

max(Namemiddle) as NameMiddle,

max(Namepreferred) as NamePreferred,

max(NameLast) as NameLast

from

(

SELECT

N.PersonID,

(SELECT Name WHERE nameTypeID = 7780 ) AS NameFirst, (SELECT Name where nametypeid = 7781 )AS NameMiddle , (SELECT Name WHERE nameTypeID = 7783 ) AS NamePreferred , (SELECT Name WHERE nameTypeID = 7779 )AS NameLast FROM Name N INNER JOIN Person p on p.IdentityID=PersonID where personid = 11701 -- FOR TESTING PURPOSES GROUP BY N.PersonID, NameID, N.Name, n.nametypeid

) as mytable group by PersonID

This returned the requre rowset for me. Once again thanks.