conditional SQL output

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

I know that a Case statement can be used to control output, but
this is a little different.

For example, there is a column that contains some number of 1 character codes.
Let's say: Optionscolumn contains "ABCDEFG"

CASE WHEN Optionscolumn like '%A%' THEN 'yes' else '' END As ContainsA
CASE WHEN Optionscolumn like '%B%' THEN 'yes' else '' END As ContainsB

That words just fine, but in the case of no value, I want no column at all included.
So, if Optionscolumn is not like '%A%' then the column "ContainsA" is not in the query results at all.

Reason is that there are over 30 possible values, and I only want to include the columns for
values found, which would be around 5 or 6. I don't want 25 or so empty columns.

I've looked and tried, can't figure it out.

Since the columns needed would be different on different rows, maybe it's not possible, and
the only way is to make a temp table and drop any columns that are entirely empty?

Any thoughts?
Thanks!

awalkintheparkAsked: 2017-11-08

Who is Participating?

Solutions

LVL 38

Pawan KumarConnect With a Mentor Database ExpertCommented: 2017-11-08

Please try full tested solution for you. I dont think any other option will work here.

Sample Data for you

CREATE TABLE SampleTest
(
	 ID INT 
	,Optionscolumn VARCHAR(10)
)
GO

INSERT INTO SampleTest VALUES
(1,'a'),
(2,'b'),
(3,'x')
GO

SELECT ID
	, CASE WHEN CHARINDEX('a', Optionscolumn,0) > 0 THEN Optionscolumn END cola
	, CASE WHEN CHARINDEX('b', Optionscolumn,0) > 0 THEN Optionscolumn END colb
	, CASE WHEN CHARINDEX('c', Optionscolumn,0) > 0 THEN Optionscolumn END colc
INTO #temp FROM SampleTest

SELECT name INTO #loop
FROM   tempdb.sys.columns
WHERE  object_id = Object_id('tempdb..#temp'); 

DECLARE @ColName as varchar(100) = ''
DECLARE @SQL AS VARCHAR(MAX) = ''

WHILE EXISTS ( SELECT TOP 1 1 FROM #loop )
BEGIN

	SELECT TOP 1 @ColName = name FROM #loop

	EXEC ( '
	
	 DECLARE @ColCnt AS INT=0
	
	 SELECT @ColCnt = COUNT(*) FROM #temp WHERE  ' + @ColName + ' IS NOT NULL ;' + '
	 
	 IF @ColCnt = 0 
		 ALTER TABLE #temp DROP COLUMN ' +  @ColName + '
	 
	 ' )
    
	DELETE FROM #loop WHERE Name = @ColName

END

Open in new window

OUTPUT of TEMP TABLE - before

/*------------------------
SELECT * FROM #temp
------------------------*/
ID          cola       colb       colc
----------- ---------- ---------- ----------
1           a          NULL       NULL
2           NULL       b          NULL
3           NULL       NULL       NULL

(3 row(s) affected)

Open in new window

OUTPUT of temp table now

SELECT * FROM #temp
	
/*------------------------
SELECT * FROM #temp
------------------------*/
ID          cola       colb
----------- ---------- ----------
1           a          NULL
2           NULL       b
3           NULL       NULL

(3 row(s) affected)

Open in new window

LVL 50

PortletPaulfreelancerCommented: 2017-11-08

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

LVL 50

PortletPaulfreelancerCommented: 2017-11-08

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

Open in new window

LVL 21

NerdsOfTechTechnology ScientistCommented: 2017-11-08

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%'

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]';

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 */

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.

LVL 50

PortletPaulfreelancerCommented: 2017-11-08

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

LVL 21

NerdsOfTechTechnology ScientistCommented: 2017-11-08

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?

awalkintheparkAuthor Commented: 2017-11-08

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

LVL 50

PortletPaulfreelancerCommented: 2017-11-08

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    |

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.

awalkintheparkAuthor Commented: 2017-11-08

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.

awalkintheparkAuthor Commented: 2017-11-08

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!

LVL 50

PortletPaulfreelancerCommented: 2017-11-08

>>" 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.

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-08

@Author - Welcome.

This is a very different and nice requirement :)

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

LVL 21

NerdsOfTechTechnology ScientistCommented: 2017-11-08

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|

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

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|

Open in new window

LVL 21

NerdsOfTechTechnology ScientistCommented: 2017-11-08

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

awalkintheparkAuthor Commented: 2017-11-12

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!