Cross Apply query problem

Question

Cross Apply query problem

tinamiller1

SSC-Addicted

Points: 496
More actions
March 1, 2017 at 1:02 pm

#341265

I have a cross apply query I have performed on my table that has data that looks like this:
Name      Function      Line      State      Contact      Address
IYE          CR              COM      NY          Sarah       111 Jones Str
IYE          DM              MCR      CA          Sarah       111 Jones Str
IYE          UM              MCD      FL           Sarah       111 Jones Str
I did my cross apply query and because the function, line, and state are char it ends up looking like this
Name     Function                                    Line                                                  State                                            Contact     Address
IYE         CR     ,     DM     ,     UM     ,     COM     ,     MCR     ,     MCD     ,     NY     ,     CA     ,     FL     ,          Sarah        111 Jones Str
I am trying to make it look like:
Name     Function          Line                        State                Contact     Address
IYE        CR, DM, UM    COM, MCR, MCD   NY, CA, FL      Sarah        111 Jones Str
I have tried substr ltrim rtrim and nothing seems to work. I have tried incorporating the 3 in my original cross apply and then after the fact when I put the cross apply into another SQL table. I have over all 7910 rows and with the cross apply end up down to 698 rows making the Name distinct and then cross applying everything else

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

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

drew.allen SSC Guru Points: 76995 More actions · Answer 1

We can't tell you what is wrong with your query if you don't provide your query. You should also post data in a CONSUMABLE format. That is, post a script that will insert your data into a table (preferably a temp table or table variable) as outlined in the first link in my signature.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Phil Parkin SSC Guru Points: 246971 More actions · Answer 2

Apart from a few commas here and there, your two rows of results look the same. Try to imagine yourself looking at your post for the first time – it's very difficult to offer any meaningful advice, based on what you've given us. Were you expecting us to somehow fix your absent query?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tinamiller1 SSC-Addicted Points: 496 More actions · Answer 3

I figured it out. Now just need to code for the trailing comma. I thought the information would come over without making it look like it does above. However it did not so I will attach the file with add to see the before, during, after and then also post my code to help others.

select distinct DelegatesName, MSOName, delegatesfunctions as DelegatesFunction, lobabbrevs as LOBAbbrev, markets as Market, ContactNames as ContractName, addressline1s as AddressLine1, AddressLine2s as AddressLine2,

Citys as City, States as State, ZipCodes as ZipCode, Phones as Phone, ContactPhoneExts as ContactPhoneExt, ContactEmails as ContactEmail, DelegatesServiceDescs as DelegateServiceDesc, Onsite, VendorAuditLocations as VendorAuditLocation,

ICEAffiliation, CurrentStatuss as CurrentStatus, accreditationmanagernames as AccreditationManagerName, Fax, DelegatesWebsite, Origins as Origin, SubDelegateNames as SubDelegateName,

DelegatePhysicalAddress1s as DelegatePhysicalAddress, DelegatePhysicalAddress2s as DelegatePhysicalAddress2, DelegatePhysicalCitys as DelegatePhysicalCity, delegatephysicalzipcodes as DelegatePhysicalZipCode,

DelegatePhone2, accreditationheldncqas as AccreditationHeldNCQA, DoestheDelegateSubDelegates as DoestheDelegatesSubDelegate, CurrentAuditStatus, ContractEffDate, contracttermdate

into anthemq.dbo.DelegatesReport

from anthemq.dbo.delegates d1

cross apply ( select distinct

left(delegatesfunction, len(delegatesfunction)-0) + ', '

from anthemq.dbo.delegates d2

where d1.delegatesname = d2.delegatesname

for XML path ('')) as t2 (delegatesfunctions)

cross apply (select distinct

left(lobabbrev, LEN(lobabbrev)-0) + ', '