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;

11701 Shirlie NULL NULL NULL

11701 NULL Patricia NULL NULL

11701 NULL NULL NULL Parkinson

11701 NULL NULL NULL

Really I need the following;

11701 Shirlie 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: 2115 More actions · Answer 1

j nuñez

SSCrazy

Points: 2115

August 13, 2008 at 5:19 am

#857292

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: 18484 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.