We help IT Professionals succeed at work.

SQL Code

PrimusPilus
PrimusPilus asked
on
Hi,

I'm trying to create an excel sheet of people's contact details, employment, and notes from our database.

The code is below and some of the results I get are attached.

I have about 28K people in the contact database yet I'm getting over 408K results in my search because people have multiple long note entry in the History tab.

How do I code it so I get about 28K results with the various long note entries for each person in their own column?

For example:

FAKE NAME 1 | LONGNOTES1a | LONGNOTES1b| LONGNOTES1c | LONGNOTES1d |
FAKE NAME 2 | LONGNOTES2a | LONGNOTES2b| LONGNOTES2c | LONGNOTES2d |

Thanks,
Primus



select sysdba.CONTACT.PREFIX,
sysdba.CONTACT.FIRSTNAME,
sysdba.CONTACT.LASTNAME,
sysdba.CONTACT.SUFFIX,
sysdba.CONTACT.TITLE,
sysdba.ACCOUNT.ACCOUNT,
sysdba.CONTACT.WORKPHONE,
sysdba.CONTACT.HOMEPHONE,
sysdba.CONTACT.MOBILE,
sysdba.CONTACT.EMAIL,
sysdba.CONTACT.SECONDARYEMAIL,
sysdba.ADDRESS.ADDRESS1,
sysdba.ADDRESS.ADDRESS2,
sysdba.ADDRESS.CITY,
sysdba.ADDRESS.STATE,
sysdba.ADDRESS.POSTALCODE,
sysdba.ADDRESS.COUNTRY,
sysdba.CONTACT.STATUS,
sysdba.HISTORY.LONGNOTES from sysdba.CONTACT
LEFT JOIN sysdba.ACCOUNT on sysdba.CONTACT.ACCOUNTID = sysdba.ACCOUNT.ACCOUNTID
LEFT JOIN sysdba.ADDRESS on sysdba.CONTACT.ADDRESSID = sysdba.ADDRESS.ADDRESSID and sysdba.ADDRESS.ISPRIMARY = 'T'
LEFT JOIN sysdba.HISTORY on sysdba.CONTACT.CONTACTID = sysdba.HISTORY.CONTACTID

Open in new window

Book1.xlsx
Comment
Watch Question

View Solution Only

jogos

Commented:
If you have a fixed column for a specific type of LONGNOTES , example whit types 'c' and 'd'

select ... 
c.LONGNOTES as longnotes1,
d.LONGNOTES as longnotes2
from sysdba.CONTACT
LEFT JOIN sysdba.ACCOUNT on sysdba.CONTACT.ACCOUNTID = sysdba.ACCOUNT.ACCOUNTID
LEFT JOIN sysdba.ADDRESS on sysdba.CONTACT.ADDRESSID = sysdba.ADDRESS.ADDRESSID and sysdba.ADDRESS.ISPRIMARY = 'T'
LEFT JOIN sysdba.HISTORY as c on sysdba.CONTACT.CONTACTID = c.CONTACTID
                                                      and c.type = 'c'
LEFT JOIN sysdba.HISTORY as c on sysdba.CONTACT.CONTACTID = d.CONTACTID
                                                      and d.type = 'd'

Open in new window

jogos

Commented:
alias 'as c' in second join must be 'as d'
PrimusPilus

Author

Commented:
Hi Jogos,

Thank you for your response! I'm going to try it.

Just a quick question from an observation. What if I don't know how many there longnotes entries there are for a person?

Thanks,
Primus
Lowfatspread
CERTIFIED EXPERT
Top Expert 2011

Commented:
is the a sequence or some other identifier with which to order the notes?

what is the datatype of the longnotes column?
Commented:
The better way is using pivot, see http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOT%20Data%20in%20SQL%202005&referringTitle=Home on how the example I suggested is written in the pivot-way.

The trick will be to separate the pivot for longnotes  and join that result with the other joined tables.

select ...
from
left join ...
left join
( your pivot here
) as p on  sysdba.CONTACT.CONTACTID = p.CONTACTID


Then the flexible way with unknown #columns, see http://www.daniweb.com/web-development/databases/ms-sql/threads/96661
PrimusPilus

Author

Commented:
Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.