问题描述
我正在尝试获取一个原始数据集,为新数据添加列并将其转换为更传统的表结构.这个想法是让脚本提取列名称(日期)并将其放入一个新列中,然后将每个日期数据值堆叠在一起.
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 中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!