We help IT Professionals succeed at work.

I need to combine multiple columns to a single field in a comma delimited format in SQL 2005

385 Views
Last Modified: 2012-05-05
Here is a prt of my table.  The Syms columns go up to 47 with Syms48 always null and there are about 1400 ChunkIDs.

ChunkID      Syms1      Syms2      Syms3      Syms4      Syms5
1522      Scarlatina      Scarlet fever      NULL      NULL      NULL
1524      Placenta previa      NULL      NULL      NULL      NULL
1532      Barrett's esophagus      NULL      NULL      NULL      NULL
1536      Martin-Bell syndrome      NULL      NULL      NULL      NULL
1543      Respiratory syncytial virus      NULL      NULL      NULL      NULL
11460      Candidiasis      Vaginal yeast infection      Yeast infection      NULL      NULL
11461      Wrist sprain      NULL      NULL      NULL      NULL
11462      Wrist fracture      NULL      NULL      NULL      NULL
11463      Pertussis      Whooping cough      NULL      NULL      NULL
11464      Whiplash      NULL      NULL      NULL      NULL
11465      Farsightedness      Hyperopia      Myopia      Nearsightedness      NULL
11467      Viral sore throat      Viral sore throat (Viral pharyngitis)      NULL      NULL      NULL
11469      Aseptic meningitis      Viral meningitis      NULL      NULL      NULL
11471      Vertigo      NULL      NULL      NULL      NULL
11474      Varicose veins      NULL      NULL      NULL      NULL
11476      Urinary tract infection (UTI)      NULL      NULL      NULL      NULL
11477      Pelvic floor hernia      Pudendal hernia      Uterine prolapse      NULL      NULL
11479      Endometrial cancer      Uterine cancer      NULL      NULL      NULL

What I need to do with this is take all the Syms colums for each ID and put them a comma delimited list in a single column called Syms.  So I need it to look like this.

ChunkID      Syms
1522      Scarlatina,Scarlet fever
1524      Placenta previa
1532      Barrett's esophagus
1536      Martin-Bell syndrome
1543      Respiratory syncytial virus
11460      Candidiasis,Vaginal yeast infection,Yeast infection
11461      Wrist sprain
11462      Wrist fracture
11463      Pertussis,Whooping cough
11464      Whiplash
11465      Farsightedness,Hyperopia      Myopia,Nearsightedness
11467      Viral sore throat,Viral sore throat (Viral pharyngitis)
11469      Aseptic meningitis,Viral meningitis
11471      Vertigo
11474      Varicose veins
11476      Urinary tract infection (UTI)
11477      Pelvic floor hernia,Pudendal hernia,Uterine prolapse
11479      Endometrial cancer,Uterine cancer

It would be great if it could be done dynamicly because of the number of columns in this table and the fact that not all ChunkIDs have the same number of Syms.  I bet this is simple bit it is over my beginner head.

Thanks in Advance for any and all help,
JasonM
Comment
Watch Question

Something like this will work:

;with CommaData as(select ChunkID,
isnull(Syms1+',','')+isnull(Syms2+',','')+isnull(Syms3+',','')+isnull(Syms4+',','')+isnull(Syms5+',','') cd
from YourTable)
select chunkID, left(cd,len(cd)-1)
from CommaData
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
you can use SSIS to do this...right click the DB, go to all tasks, then export.  In wizard, specify your table name as the source and a flat file as the destination.  From there, it will create a .csv file for you.

Author

Commented:
BrandonGalderisi

Your solution creates the dataset I'm looking for, however the Syms columns go up to Syms47.  Is there anyway to dynamicly get those columns in there so I do not have to type out +isnull(Syms5+',','')+isnull(Syms6+',','')+isnull(Syms7+',','') over and over again.  Thanks for the help so far.

JasonM
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
..and hopefully none of the Syms columns are integers...  :)
CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
None of them are int...they are all text.
I'm working on something cooler maybe.... i have to bounce something off of chap first.
cooler as in a cooler way to do this for you.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks all for the help.  That is a very cool way to do it.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.