基于table1创建新视图：转换类别列结构并拆分行数据

To achieve the desired view that splits each row of table1 into two rows (one for each category column), you can use UNION ALL to combine two select statements—each pulling one category-value pair along with the original ID.

Here's the SQL code to create the view:

CREATE VIEW split_category_view AS
SELECT 
    ID,
    'category1' AS category,
    category1 AS value
FROM table1
UNION ALL
SELECT 
    ID,
    'category2' AS category,
    category2 AS value
FROM table1;

How this works: #

• The first SELECT statement takes each row from table1 and maps it to a row where category is explicitly set to 'category1' and value is the content of the category1 column.
• The second SELECT does the same for category2, creating a row with 'category2' as the category and the corresponding category2 value.
• UNION ALL combines these two result sets into a single view, preserving all rows (unlike UNION which would remove duplicates, which we don't want here).

Example Output: #

For your sample data:

The view will return:

This approach is straightforward and works across most SQL databases (MySQL, PostgreSQL, SQL Server, etc.).

内容的提问来源于stack exchange，提问作者Nwn