将数据从一个表导出到另一个具有不同结构的表中 [英] Exporting data from one table into another table with a different structure
问题描述
大家好，
首先，我想向任何错误/误解道歉，因为英语不是我的自然语言。
背景：我需要提取存储在一个表中的数据（实际上它是连接两个表的结果）并将其显示在报表中。请在下面找到我想要显示的数据。
假设我有一个包含以下结构和数据的表格：
------------------------
|代码|％| nCol | nDel | day |
------------------------
101 | 100 | 4 | 4 | SUN |
------------------------
102 | 100 | 4 | 4 | SUN |
------------------------
104 | 100 | 6 | 6 | MON |
------------------------
101 | 50 | 4 | 8 | TUE |
------------------------
102 | 50 | 4 | 8 | TUE |
------------------------
104 | 100 | 2 | 2 | TUE |
------------------------
105 | 50 | 1 | 2 | TUE |
------------------------
106 | 100 | 2 | 2 | TUE |
------------------------
等......
我希望得到一张包含以下结构和数据的表格：
| code | SUNcol | SUNdel | SUN％| MONcol | MONdel | mon％| TUEcol | TUEdel | TUE％|依此类推至星期六
-------------------- -------------------------------------------
| 101 | 4 | 4 | 100 | 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------- -----------------------
| 102 | 4 | 4 | 100 | 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------- -----------------------
| 103 | 0 | 0 | 0 | 6 | 6 | 100 | 2 | 2 | 100 |
-------------------------------------- -------------------------
| 104 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 50 |
---------------------------------------- -----------------------
| 105 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 100 |
-------------------------------------- -------------------------
结果表（紧接在上面）应该始终从星期日到星期六呈现整个星期。
我设法通过对第一个表运行的游标并将数据注入到结果表中来实现，构建为临时表。但是我想避免使用游标。
对此有何帮助？
非常感谢。
PS我正在使用Sql Server 2014
我尝试过：
我在Codeproject和StackOverflow上搜索。
Hi All,
First I would like to apologise any mistake/misunderstanding as English is not my natural language.
Context: I need to pull data stored in one table(actually it is a result of joining two tables) and displaying it in a report. Please find below what I would like to accomplish in order to display the data.
Let say I have a table with the following structure and data in it:
------------------------
|code|% |nCol|nDel|day|
------------------------
101 |100| 4 | 4 |SUN|
------------------------
102 |100| 4 | 4 |SUN|
------------------------
104 |100| 6 | 6 |MON|
------------------------
101 |50 | 4 | 8 |TUE|
------------------------
102 |50 | 4 | 8 |TUE|
------------------------
104 |100| 2 | 2 |TUE|
------------------------
105 |50 | 1 | 2 |TUE|
------------------------
106 |100| 2 | 2 |TUE|
------------------------
and so on...
I would like to have as a result a table with the following structure and data in it:
|code|SUNcol|SUNdel|SUN%|MONcol|MONdel|mon%|TUEcol|TUEdel|TUE%|and so on till saturday
---------------------------------------------------------------
|101 | 4 | 4 | 100| 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------------------------------
|102 | 4 | 4 | 100| 0 | 0 | 0 | 4 | 8 | 50 |
---------------------------------------------------------------
|103 | 0 | 0 | 0 | 6 | 6 | 100| 2 | 2 |100 |
---------------------------------------------------------------
|104 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 50 |
---------------------------------------------------------------
|105 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 |100 |
---------------------------------------------------------------
The result table(immediately above) should always present the entire week from Sunday till saturday.
I manage to do it through a cursor running against the first table and injecting the data into the result table, built as a temporary table. But I would like to avoid the use of cursors.
Any help with this?
Many thanks.
P.S. I'm using Sql Server 2014
What I have tried:
I have search on Codeproject and StackOverflow.
推荐答案
将列数据转换为行数据的一种方法是INNER将表连接到自身，多次
One method of converting column data into row data is to INNER JOIN the table to itself, multiple times
SELECT A.someVal, A.colx, B.colx, C.colx . . . .
FROM Table1 as A
INNER JOIN Table1 as B
ON A.someVal=B.someVal
INNER JOIN Table1 as C
on A.someVal = C.someVal
.
.
.
WHERE (etc.)
这篇关于将数据从一个表导出到另一个具有不同结构的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
