如何以这种格式从表中获取数据 [英] how to fetch data from table in this format
问题描述
现有表格
部门1部门2部门3部门4部门5
类别1类别1类别1类别1类别1
类别2类别2类别2类别2类别2
类别3类别3类别3类别3类别3
类别4类别4类别4类别4类别4
我现在有这个表我想以这种格式获取数据，以便列指定这样的行
部门1
类别1
部门1
类别2
部门1
类别3
部门1
类别4
部门2
类别1
部门1
类别2
部门2
类别2
部门2
类别3
部门2
类别4
部门3
类别1
部门3
类别2
部门3
类别3
部门3
类别4
部门4
类别1
Existing Table
Department1 Department2 Department3 Department4 Department5
Category 1 Category 1 Category 1 Category 1 Category 1
Category 2 Category 2 Category 2 Category 2 Category2
Category 3 Category 3 Category 3 Category 3 Category3
Category 4 Category 4 Category 4 Category 4 Category 4
I have this table now i want to fetch data in this format so that column specify row like this
Department1
Category 1
Department 1
Category 2
Department 1
Category 3
Department 1
Category 4
Department 2
Category 1
Department 1
Category 2
Department 2
Category 2
Department 2
Category 3
Department 2
Category 4
Department 3
Category 1
Department 3
Category 2
Department 3
Category 3
Department 3
Category 4
Department 4
Category 1
推荐答案
首先，让我们指出此表不符合第3范式。根据您提供的内容，模型应该是3个表Departments，Categories以及两个字段之间的链接，其中两个字段包含来自其他表的键以用于链接的记录。然后，数据可以以其当前格式显示为透视查询，或者以新的格式显示内部连接。
如果您遇到当前模型，则以下会给你你所要求的：
First off, let's point out that this table does not conform to 3rd Normal Form. From what you present, the model should be 3 tables "Departments", "Categories" and a link between the two with two fields containing the keys from the other tables for records it links. The data could then be displayed in its current format as a pivot query or in your new format with inner joins.
If you are stuck with the current model then the following will give you what you ask for:
SELECT 'Department 1' AS Department, Department1 AS Category
FROM MyTable
UNION
SELECT 'Department 2' AS Department, Department2 AS Category
FROM MyTable
UNION
SELECT 'Department 3' AS Department, Department3 AS Category
FROM MyTable
UNION
SELECT 'Department 4' AS Department, Department4 AS Category
FROM MyTable
UNION
SELECT 'Department 5' AS Department, Department5 AS Category
FROM MyTable
注意：
这将很快如果你有更多的列，那么就会耗尽蒸汽并慢慢爬行很多行，但它会工作。它也非常笨拙。
如果模型不可协商且添加了列，则必须修改查询。从长远来看，您可能希望编写一个CLR（.NET）表值函数来执行模式查询并动态组装数据。
Notes:
This will pretty soon run out of steam and slow to a crawl if you have a lot more columns and a lot of rows, but it will work. It's also very clumsy.
If the model is not negotiable and columns get added, the query must be modified. In the long run you might want to write a CLR (.NET) Table Valued Function that performs a schema query and assembles the data dynamically.
这篇关于如何以这种格式从表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
