SQL to extract multiple addresses

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL to extract multiple addresses

Author

Topic

Colweb
Starting Member

4 Posts

Posted - 2012-07-04 : 12:01:24

I am attempting to list the Primary Contact's [Pc] Postal Address [PA], Study Address [SA] and Cellphone [CN] in a row for each student.
Please assist with the SQL commands.

SELECT a.STUDENT_NUMBER AS STUDENTNO,
a.ADDRESS_LINE1 AS PALINE1,
a.ADDRESS_LINE2 AS PALINE2,
a.ADDRESS_LINE3 AS PALINE3,
a.ADDRESS_LINE4 AS PALINE4,
a.ADDRESS_POSTCODE AS PAPCODE,
'PA' AS CTYPE,
b.ADDRESS_LINE1 AS CELLNO,
'CN' AS CTYPE,
c.ADRESS_LINE1 AS SALINE1,
c.ADDRESS_LINE2 AS SALINE2,
c.ADDRESS_LINE3 AS SALINE3,
c.ADDRESS_LINE4 AS SALINE4,
c.ADDRESS_POSTCODE AS SAPCODE,
'SA' AS CTYPE
FROM N24vaddress_reg a, N24vaddress_reg b, N24vaddress_reg c
WHERE a.CONTACT_CODE = 'PC'
AND a.ADDRESS_CODE = 'PA'
AND b.CONTACT_CODE = 'PC'
AND b.ADDRESS_CODE = 'CN'
c.CONTACT CODE = 'PC'
AND c.ADDRESS_CODE = 'SA'
AND a.STUDENT_NUMBER = b.STUDENT_NUMBER
AND a.STUDENT_NUMBER = c.STUDENT_NUMBER;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-04 : 12:49:52

AND b.CONTACT_CODE = 'PC'
AND b.ADDRESS_CODE = 'CN'

That implies yoou have a cellphone contact primary address record.
I am a bit surprised that both values identify the record - I would expect there to be a primary contact record with a contact type - which may not be cellphone.
Would also expect the contact type to be CN rather than Address type.

Without knowing yoour data it's difficult to suggest anything.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 17:41:55

[code]
SELECT STUDENT_NUMBER AS STUDENTNO,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE1 END) AS PALINE1,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE2 END) AS PALINE2,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE3 END) AS PALINE3,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE4 END) AS PALINE4,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_POSTCODE END) AS PAPCODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_LINE1 END) AS CELLNO,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE1 END) AS SALINE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE2 END) AS SALINE2,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE3 END) AS SALINE3,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE4 END) AS SALINE4,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_POSTCODE END) AS SAPCODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_CODE END) AS CTYPE3
FROM N24vaddress_reg
WHERE CONTACT_CODE = 'PC'
AND ADDRESS_CODE IN ('PA','CN','SA')
GROUP BY STUDENT_NUMBER
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Colweb
Starting Member

4 Posts

Posted - 2012-07-05 : 01:52:48

Thank you very much...it worked perfectly!
Kind regards

quote:

Originally posted by visakh16


SELECT STUDENT_NUMBER AS STUDENTNO,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE1 END) AS PALINE1,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE2 END) AS PALINE2,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE3 END) AS PALINE3,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_LINE4 END) AS PALINE4,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_POSTCODE END) AS PAPCODE,
MAX(CASE WHEN ADDRESS_CODE ='PA' THEN ADDRESS_CODE END) AS CTYPE1,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_LINE1 END) AS CELLNO,
MAX(CASE WHEN ADDRESS_CODE ='CN' THEN ADDRESS_CODE END) AS CTYPE2,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE1 END) AS SALINE1,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE2 END) AS SALINE2,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE3 END) AS SALINE3,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_LINE4 END) AS SALINE4,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_POSTCODE END) AS SAPCODE,
MAX(CASE WHEN ADDRESS_CODE ='SA' THEN ADDRESS_CODE END) AS CTYPE3
FROM N24vaddress_reg
WHERE CONTACT_CODE = 'PC'
AND ADDRESS_CODE IN ('PA','CN','SA')
GROUP BY STUDENT_NUMBER

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 09:52:57

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources