Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
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 1 day ago
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 1 day ago
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 1 day ago
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
To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month7 days, 11 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.