問題描述

我正在嘗試獲取一個原始數據集，為新數據添加列并將其轉換為更傳統(tǒng)的表結構.這個想法是讓腳本提取列名稱(日期)并將其放入一個新列中，然后將每個日期數據值堆疊在一起.

I'm trying to take a raw data set that adds columns for new data and convert it to a more traditional table structure. The idea is to have the script pull the column name (the date) and put that into a new column and then stack each dates data values on top of each other.

示例

Store     1/1/2013     2/1/2013
XYZ INC   $1000        $2000

到

Store     Date         Value
XYZ INC   1/1/2013     $1000
XYZ INC   2/1/2013     $2000

謝謝

推薦答案

有幾種不同的方法可以獲得您想要的結果.

There are a few different ways that you can get the result that you want.

您可以將 SELECT 與 UNION ALL 一起使用:

You can use a SELECT with UNION ALL:

select store, '1/1/2013' date, [1/1/2013] value
from yourtable
union all
select store, '2/1/2013' date, [2/1/2013] value
from yourtable;

參見SQL Fiddle with Demo.

您可以使用 UNPIVOT 函數:

You can use the UNPIVOT function:

select store, date, value
from yourtable
unpivot
(
  value
  for date in ([1/1/2013], [2/1/2013])
) un;

參見 SQL Fiddle with Demo.

最后，根據您的 SQL Server 版本，您可以使用 CROSS APPLY:

Finally, depending on your version of SQL Server you can use CROSS APPLY:

select store, date, value
from yourtable
cross apply
(
  values
    ('1/1/2013', [1/1/2013]),
    ('2/1/2013', [2/1/2013])
) c (date, value)

請參閱SQL Fiddle with Demo.所有版本都會給出以下結果:

See SQL Fiddle with Demo. All versions will give a result of:

|   STORE |     DATE | VALUE |
|---------|----------|-------|
| XYZ INC | 1/1/2013 |  1000 |
| XYZ INC | 2/1/2013 |  2000 |

這篇關于如何將列轉置為 sql server 中的行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！