how can I output with additional information in a specific row in sql-CodePudding

For example: there is a lot of food stored in the database in this format:

kind      | name
---------- ----------
fruit     | apple
fruit     | banana
vegetable | lettuce
vegetable | onion

I want to select all vegetables and add a tag behind them like:

apple
banana
lettuce (vegetable)
onion (vegetable)

I can use SELECT to select all vegetables:

SELECT name 
FROM "food" 
WHERE kind LIKE "vegetable";

But how can I add a tag behind the vegetable?

Hope someone can give me a hint

CodePudding user response：

In SQLite you have to use the || operator:

SELECT name || ' (' || kind || ')' FROM food WHERE kind like 'vegetable';

CodePudding user response：

Use a CASE expression for the vegetables:

SELECT name ||
       CASE 
         WHEN kind = 'vegetable' THEN ' (' || kind || ')' 
         ELSE '' 
       END AS name 
FROM food;

CodePudding user response：

My basic answer would be to use Concat:
SELECT CONCAT(name," (", kind, ")") FROM "food" WHERE kind like "vegetable";