更改表格格式 [英] Changing the format of a table

问题描述

大家好，

我有下面的excel表，其中包含我们员工的日程表数据，我将表按原样(第一行作为列)导入到我们的数据库中，然后使用该表在Web和用户应用程序中显示数据.

< img src ="https://lh4.googleusercontent.com/-dUl0EarsTAAA4/Tfdc /xcoy0MXBU1I/s144/tablesample.gif"height =" 43"width =" 144"/>

但是，以这种方式格式化的数据通常不太容易查询或使用.我想以某种方式将这些数据处理到以下格式化的表中.

https://lh6.googleusercontent.com/-hAasVFQrxQo/TfuaQJDGuqM/EM1AAA2PEpe/png

我知道第一次从excel导入数据时可以通过查询来格式化数据，但答案并没有传给我.
谢谢，

Skatebkp

Hi guys,

I have the following excel table which contains schedule data for our employees, I import the table into our database as is(1st row as columns) and then I use the table to display data in web and user applications.

<img src="https://lh4.googleusercontent.com/-dUl0EarsTs4/TfdcgpkSnMI/AAAAAAAAAOs/xcoy0MXBU1I/s144/tablesample.gif" height="43" width="144" />

But the data formatted in this way is not very easy to query or work with in general. I would like to somehow manipulate this data into the following formatted table.

https://lh6.googleusercontent.com/-hAasVFQrxQo/TfuaQJDGuqI/AAAAAAAAAPE/Mnd1kXYb7EM/ttable2.png

I know it could be possible with a query to format the data when I Import it the 1st time from excel but the answer is not coming to me.
thanks,

skatebkp

推荐答案

在SQL Server中，您可以使用PIVOT-UNPIVOT

使用PIVOT和UNPIVOT [在SQL Server中透视数据 [

In SQL server you can use PIVOT - UNPIVOT

Using PIVOT and UNPIVOT[^]

Pivoting data in SQL Server[^](Alternate ways)

以下查询完全按照我的要求显示数据:

The following query displays the data exactly as I want it:

SELECT Agent, Date, Shift
FROM
(SELECT Agent,[01/06/11],[02/06/11],[03/06/11],[04/06/11]) from shiftTable


UNPIVOT
(Shift FOR Date IN

([01/06/11],[02/06/11],[03/06/11],[04/06/11])
)as unpvt;

这篇关于更改表格格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！