Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.
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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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.
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
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%'
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]';
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 */
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
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?
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
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 |
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.
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.
Please try full tested solution for you. I dont think any other option will work here.
Sample Data for you
OUTPUT of TEMP TABLE - before
OUTPUT of temp table now
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
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)
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)
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!
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.
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.
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.
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
/*search for A, E, I, or U; return highest priority*/
| ID | Codes |PriorityMatch|
| Row1 | Y^BIWCAER |A|
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
30 possible columnsYou 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|
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!
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial