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:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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

0
 
PortletPaulfreelancerCommented:
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
0
 
PortletPaulfreelancerCommented:
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

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
NerdsOfTechTechnology ScientistCommented:
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.
0
 
PortletPaulfreelancerCommented:
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
0
 
NerdsOfTechTechnology ScientistCommented:
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?
0
 
awalkintheparkAuthor Commented:
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
0
 
PortletPaulfreelancerCommented:
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.
0
 
awalkintheparkAuthor Commented:
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.
0
 
awalkintheparkAuthor Commented:
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!
0
 
PortletPaulfreelancerCommented:
>>" 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.
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Welcome.

This is a very different and nice requirement :)

Please try and let us know if you face any issues.
0
 
NerdsOfTechTechnology ScientistCommented:
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

0
 
NerdsOfTechTechnology ScientistCommented:
Sorry for the multiple edits. Please refresh to view suggestion above.
0
 
awalkintheparkAuthor Commented:
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!
0
 
awalkintheparkAuthor Commented:
Thanks to everyone for the ideas.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.