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.
我尝试使用一个原始数据集，为新数据添加列，并将其转换为更传统的表结构。我们的想法是让脚本提取列名(日期)并将其放入一个新的列中，然后将每个日期数据值叠加在一起。
Example
例子
Store 1/1/2013 2/1/2013
XYZ INC $1000 $2000
To
来
Store Date Value
XYZ INC 1/1/2013 $1000
XYZ INC 2/1/2013 $2000
thanks
谢谢
3 个解决方案
#1
6
There are a few different ways that you can get the result that you want.
有几种不同的方法可以得到你想要的结果。
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;
See SQL Fiddle with Demo.
参见SQL小提琴演示。
You can use the UNPIVOT function:
你可以使用UNPIVOT函数:
select store, date, value
from yourtable
unpivot
(
value
for date in ([1/1/2013], [2/1/2013])
) un;
See SQL Fiddle with Demo.
参见SQL小提琴演示。
Finally, depending on your version of SQL Server you can use CROSS APPLY:
最后，根据您的SQL Server版本，您可以使用交叉应用:
select store, date, value
from yourtable
cross apply
(
values
('1/1/2013', [1/1/2013]),
('2/1/2013', [2/1/2013])
) c (date, value)
See SQL Fiddle with Demo. All versions will give a result of:
参见SQL小提琴演示。所有版本将产生以下结果:
| STORE | DATE | VALUE |
|---------|----------|-------|
| XYZ INC | 1/1/2013 | 1000 |
| XYZ INC | 2/1/2013 | 2000 |
#2
0
Depending on the details of the problem (i.e. source format, number and variability of dates, how often you need to perform the task, etc), it very well may be much easier to use some other language to parse the data and perform either a reformatting function or the direct insert into the final table.
根据的细节问题(即源格式、日期、数量和可变性的频率需要执行的任务,等),它很可能会更容易使用其他语言解析数据并执行重新格式化函数或直接插入最后的表。
The above said, if you're interested in a completely SQL solution, it sounds like you're looking for some dynamic pivot functionality. The keywords being dynamic SQL and unpivot. The details vary based on what RDBMS you're using and exactly what the specs are on the initial data set.
上面说，如果您对一个完整的SQL解决方案感兴趣，它听起来就像您在寻找一些动态的pivot功能。关键字是动态SQL和unpivot。具体细节取决于您使用的RDBMS，以及初始数据集上的规范。
#3
-1
I would use a scripting language (Perl, Python, etc.) to generate an INSERT statement for each date column you have in the original data and transpose it into a row keyed by Store and Date. Then run the inserts into your normalized table.
我将使用脚本语言(Perl、Python等)为原始数据中的每个日期列生成一个INSERT语句，并将其转换为按存储和日期键控的行。然后将插入运行到规范化表中。
#1
6
There are a few different ways that you can get the result that you want.
有几种不同的方法可以得到你想要的结果。
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;
See SQL Fiddle with Demo.
参见SQL小提琴演示。
You can use the UNPIVOT function:
你可以使用UNPIVOT函数:
select store, date, value
from yourtable
unpivot
(
value
for date in ([1/1/2013], [2/1/2013])
) un;
See SQL Fiddle with Demo.
参见SQL小提琴演示。
Finally, depending on your version of SQL Server you can use CROSS APPLY:
最后，根据您的SQL Server版本，您可以使用交叉应用:
select store, date, value
from yourtable
cross apply
(
values
('1/1/2013', [1/1/2013]),
('2/1/2013', [2/1/2013])
) c (date, value)
See SQL Fiddle with Demo. All versions will give a result of:
参见SQL小提琴演示。所有版本将产生以下结果:
| STORE | DATE | VALUE |
|---------|----------|-------|
| XYZ INC | 1/1/2013 | 1000 |
| XYZ INC | 2/1/2013 | 2000 |
#2
0
Depending on the details of the problem (i.e. source format, number and variability of dates, how often you need to perform the task, etc), it very well may be much easier to use some other language to parse the data and perform either a reformatting function or the direct insert into the final table.
根据的细节问题(即源格式、日期、数量和可变性的频率需要执行的任务,等),它很可能会更容易使用其他语言解析数据并执行重新格式化函数或直接插入最后的表。
The above said, if you're interested in a completely SQL solution, it sounds like you're looking for some dynamic pivot functionality. The keywords being dynamic SQL and unpivot. The details vary based on what RDBMS you're using and exactly what the specs are on the initial data set.
上面说，如果您对一个完整的SQL解决方案感兴趣，它听起来就像您在寻找一些动态的pivot功能。关键字是动态SQL和unpivot。具体细节取决于您使用的RDBMS，以及初始数据集上的规范。
#3
-1
I would use a scripting language (Perl, Python, etc.) to generate an INSERT statement for each date column you have in the original data and transpose it into a row keyed by Store and Date. Then run the inserts into your normalized table.
我将使用脚本语言(Perl、Python等)为原始数据中的每个日期列生成一个INSERT语句，并将其转换为按存储和日期键控的行。然后将插入运行到规范化表中。