Solved

SQL Syntax

Posted on 2009-05-19
7
289 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:healthcheckinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24423322
what is the database/version you are using?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24423327
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24423358
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 11

Expert Comment

by:bmatumbura
ID: 24423359
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24423374
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
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24423378
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
 

Author Closing Comment

by:healthcheckinc
ID: 31583062
its needed for a report..I didnt design the system
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question