Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Syntax
Posted on 2009-05-19
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
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
[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
3
2
- +1
7 Comments
Active today
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
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
Active today
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
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
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
Active today
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
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
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
Featured Post
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.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 17 hours left to enroll
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.