SQL Syntax

I have a table with mulitple columns of doc types that either has a 0 or 1. I have created another table and I want to put all the 1s concatenated into the docname field of the new table. Example

1st table
acctID   doc1   doc2   doc3
123         0        1           1

I need to pull it like this into my 2nd table
acctID      docs
123        doc2, doc3


I know this is easy but I have spent too much time on it already.....Any help would be greatly appreciated
healthcheckincAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Patrick MatthewsConnect With a Mentor Commented:
SQL Server...

SELECT acctID, SUBSTRING(
      CASE WHEN doc1 = 0 THEN '' ELSE ', doc1' END +
      CASE WHEN doc2 = 0 THEN '' ELSE ', doc2' END +
      CASE WHEN doc3 = 0 THEN '' ELSE ', doc3' END, 3, 100) AS Docs
FROM SomeTable

To create a new table...

SELECT acctID, SUBSTRING(
      CASE WHEN doc1 = 0 THEN '' ELSE ', doc1' END +
      CASE WHEN doc2 = 0 THEN '' ELSE ', doc2' END +
      CASE WHEN doc3 = 0 THEN '' ELSE ', doc3' END, 3, 100) AS Docs
INTO NewTableName
FROM SomeTable
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the database/version you are using?
0
 
Patrick MatthewsCommented:
Hello healthcheckinc,

With respect, both of these are reall, really suboptimal designs :)

What db product are you using, as it makes a difference in syntax...

Regards,

Patrick
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Take the quiz
 
bmatumburaCommented:
Try this
SELECT AcctID, 
CASE WHEN doc1 AND doc2 AND doc3 THEN 'doc1, doc2, doc3'
CASE WHEN doc1 AND doc2 THEN 'doc1, doc2'
CASE WHEN doc1 and doc3 THEN 'doc1, doc3'
CASE WHEN doc2 AND doc3 THEN 'doc2, doc3'
CASE WHEN doc1 THEN 'doc1'
CASE WHEN doc2 THEN 'doc2'
ELSE 'doc3' END AS docs
INTO Table2 from Table1

Open in new window

0
 
Patrick MatthewsCommented:
Access...

SELECT acctID, Mid(IIf(doc1 = 0, "", ", doc1") & IIf(doc2 = 0, "", ", doc2") & IIf(doc3 = 0, "", ", doc3"), 3) AS docs
FROM SomeTable

creating new table...

SELECT acctID, Mid(IIf(doc1 = 0, "", ", doc1") & IIf(doc2 = 0, "", ", doc2") & IIf(doc3 = 0, "", ", doc3"), 3) AS docs
INTO NewTable
FROM SomeTable
0
 
bmatumburaCommented:
correction
SELECT AcctID, 
CASE WHEN doc1 AND doc2 AND doc3 THEN 'doc1, doc2, doc3'
CASE WHEN doc1 AND doc2 THEN 'doc1, doc2'
CASE WHEN doc1 and doc3 THEN 'doc1, doc3'
CASE WHEN doc2 AND doc3 THEN 'doc2, doc3'
CASE WHEN doc1 THEN 'doc1'
CASE WHEN doc2 THEN 'doc2'
CASE WHEN doc3 THEN 'doc3'
ELSE '' END AS docs
INTO Table2 from Table1

Open in new window

0
 
healthcheckincAuthor Commented:
its needed for a report..I didnt design the system
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.