conditional SQL output

To change the column names - at all - you need either:

 1. Dynamic SQL
 2. Multiple queries and some branching logic.

Since the columns needed would be different on different rows
This simply is not possible in a single query

IF you add a small sample you data (just small few rows, few columns) - and - from that sample a mockup of what you would like as a result, then we may be able to suggest alternatives.

Just copy/paste the sample and expected result, or use Excel, but easily parsed text is good.

MyTable
ID col1 col 2 col3
1  x    y     z

Select all Open in new window

Any way to write a query that will conditionally include a column?

No, but you could make the column value NULL for post-processing and filter to include records only where columns are matches via LIKE or REGEXP.

This is an extremely taxing way of searching the table, however, you could add a WHERE clause to the end of that which matches your case columns to reduce the records which have none of the conditions matched:

SELECT *,
CASE WHEN Optionscolumn  like '%A%'  THEN 'yes' else ''  END  As ContainsA, 
CASE WHEN Optionscolumn  like '%B%'  THEN 'yes' else ''  END  As ContainsB, 
CASE WHEN Optionscolumn  like '%C%'  THEN 'yes' else ''  END  As ContainsC,
FROM table1
WHERE 
Optionscolumn Like '%A%' 
OR Optionscolumn Like '%B%' 
OR Optionscolumn Like '%C%'

Select all Open in new window

Instead consider a WHERE with REGEXP:

SELECT *,
CASE WHEN Optionscolumn  like '%A%'  THEN 'yes' else ''  END  As ContainsA, 
CASE WHEN Optionscolumn  like '%B%'  THEN 'yes' else ''  END  As ContainsB, 
CASE WHEN Optionscolumn  like '%C%'  THEN 'yes' else ''  END  As ContainsC,
FROM table1
WHERE 
Optionscolumn  REGEXP '[ABC]';

Select all Open in new window

Or if you want to return rows that match a pattern consider a WHERE with REGEXP by itself:

SELECT *
FROM table1
WHERE 
Optionscolumn  REGEXP '[AEIOUY]'; /* return rows only if A, E, I, O, U, or Y is in Optionscolumn */

Select all Open in new window

https://dev.mysql.com/doc/refman/5.7/en/regexp.html

What characters are you looking for? We might be able to help you better if we knew more about what you are trying to accomplish.

Could we wait for a sample?  It's so unproductive plying the guessing game (e.g. maybe listagg() solves this or json?, who knows).

By the way. What database is this for? Oracle? Postgres? (something with regex , but which one)
Query syntax differs between dbms vendors

You're right PortletPaul. We need sample and database name.

Also, REGEXP is more taxing than LIKE because LIKE is able to use indexes whereas REGEXP does not.
1.) Is the Optionscolumn indexed?

I used REGEXP as an example in case you need more complex matches.
2.) What are your most complicated searches?

Here's a sample.
The table would consist of row of columns A & B
the spreadsheet shows what the output would be if you
rewrote this query to include the answer to the posted question:

Select  ID, Codes,  (code parsing - column creating TSQL code goes here)  from  SampleTable

The output would look like the spreadsheet.
There would be tests for the presence of quite a few more codes. The "Has" columns would
only be created for codes that were found in the codes column.

Different recordsets containing different codes would have a different set of  "has" codes.
Hope this helps.
My alternative is to test for all codes and include a column for all, but I'd rather not have
a lot of empty columns.
I don't know, perhaps 5 thousand records would have a least one of every  code.  
Regardless, I'd still like to solve this. Or get help from you people!

We allow users to export this data, and of course they see the code column and want to know what they mean.
This is creating  code translation columns for a column that has a variable number of  codes packed in.
SQLCodeParseSample.xlsx

This is the "expected result", is that correct?

|  ID  | Codes  | HasN | HasY | Has^ | HasW | HasZ | Has~ | Has@ | Has* | HasR |
|------|--------|------|------|------|------|------|------|------|------|------|
| Row1 | Y^WR   |      | Y    | Y    | Y    |      |      |      |      | Y    |
| Row2 | N^Z~@* | Y    |      | Y    |      | Y    | Y    | Y    | Y    |      |
| Row3 | YWZ*R  |      | Y    |      | Y    | Y    |      |      | Y    | Y    |

Select all Open in new window

What does the raw source data look like? I'm still struggling to understand and this part of the puzzle only you can see.

yes that's it,  the table really only has ID and codes, the SQL magic I'm looking for would parse the codes and add the  "Has" columns that are needed based on  successfully finding codes.
Test for "A"  - found make the column
Test for "B"  - found make the column
Test for "C" - not found don't make the column
Test for "X"
Test for "Y"
Test for "Z"  etc
I think this is probably not possible, and  the columns needed for the output  need to be determined at the start of the  query execution, but the   result I want isn't known until the  codes in the entire result set  are  tested.  Maybe a multi step procedure is needed.

yeah, that's what I was starting to think would be the only way - testing  count(*)  not null then dropping.
Probably overall not a good idea. I think I'll  just out put the full set of code columns and let the users delete as needed.
I will try this out tomorrow though.
Nice, thanks!

>>" the columns needed for the output  need to be determined at the start of the  query execution"

I believe we started there did we not? That statement is true.

@Author - Welcome.

This is a very different and nice requirement :)

Please try and let us know if you face any issues.

If it has A in it is that more important than it having B; in other words, is there an importance to the character? If so you could CASE through each row and get a SINGLE column result matching the highest priority.

/*search for A, E, I, or U; return highest priority*/
|  ID  | Codes       |PriorityMatch|
| Row1 | Y^BIWCAER   |A|

Select all Open in new window

What if the output column was a concatenation of all of the matching codes? If no match, the column cell would be NULL (in this case you could even subtract the NULL results using
 

WHERE ConcatListOfMatchingCodes IS NOT NULL

Select all Open in new window

Then, you would be dealing with one column this way instead of

30 possible columns

You could then parse this list if needed (comma separated) or leave it as is as a sorted and clean list for others to read.

/*search for A, E, I, or U*/
|  ID  | Codes       |ConcatListOfMatchingCodes|
| Row1 | Y^BIWCAER   |A,E,I|

Select all Open in new window

Sorry for the multiple edits. Please refresh to view suggestion above.

The temp table is best. A column for each works with the case statement, but  empty columns.
Yes, knowing columns before , and  varying columns depending on the input is a big part of it.
 I think I'll  test the temp table method on some larger  result and see how it goes.  But
will probably use the  one column  for each possible code. consistent column layout will be better for users anyway.
Thanks  to all for your ideas!

Thanks to everyone for the ideas.