行从一个表到另一个表的列 [英] rows into columns from one table to another table
问题描述
hi
i有一个包含代码和日期的表格
代码日期
101 12/2/2014
101 13/2/2014
101 17/2/2014
102 5/2/2014
102 11/2/2014
110 2/2/2014
110 5/2/2014
110 25/2/2014
i希望逐行读取表格,并根据代码在另一个表格中更新细节。
code dt1 dt2 dt3 dt4
101 12/2/2014 13/2/2014 17/2/2014
102 5/2/2014 11/2 / 2014
110 2/2/2014 5/2/2014 25/2/2014
i需要你的帮助来完成这个任务。
提前谢谢
这是一个老问题,但我发布此解决方案计数器解决方案1使用CURSOR - 不推荐的方法(参见 SQL Server做和不要' s [ ^ ])
以下解决方案使用SQL 2005中提供的构造 - Common Table Expressions和Pivot。一些可能对这些主题有用的CP文章:
SQL SERVER 2008中的公用表表达式(CTE) [ ^ ]
在SQL查询中使用Pivot的简单方法 [ ^ ]
我的样本数据:< pre lang =sql> 创建 table t1
(
code int ,
dt date
)
INSERT t1 值
( 101 ,' 12-FEB-2014'),
( 101 ,' 13-FEB-2014'),
( 101 ,' 17-FEB-2014' ),
( 102 ,' 5-FEB-2014'),
( 102 ,' 11-FEB-2014'),
( 110 ,' 2-FEB-2014'),
( 110 ,' 5-FEB-2014'),
( 110 ,' 25-FEB-2014');查询
WITH CTE1(代码,dt,datenumber)
AS
(
SELECT [code],dt,
' dt' + CAST(ROW_NUMBER() over ( PARTITION BY 代码
ORDER BY 代码,dt ASC ) AS VARCHAR )datenumber
FROM T1
)
选择代码,dt,datenumber
进入 ## t2 来自 CTE1
SELECT *
FROM (
SELECT
代码,
dt,
datenumber
FROM ## t2
) as s
PIVOT
(
MAX(dt)
FOR [ datenumber] IN ([dt1],[dt2],[dt3],[dt4])
) AS p结果:
代码dt1 dt2 dt3 dt4
101 2014-02 -12 2014-02-13 2014-02-17 NULL
102 2014-02-05 2014-02-11 NULL NULL
110 2014-02-02 2014-02-05 2014-02-25 NULL
hi
i have one table containing the code and date in rows
code date
101 12/2/2014
101 13/2/2014
101 17/2/2014
102 5/2/2014
102 11/2/2014
110 2/2/2014
110 5/2/2014
110 25/2/2014
i want to read the table row by row and based on the code the detail should be updated in another table like this.
code dt1 dt2 dt3 dt4
101 12/2/2014 13/2/2014 17/2/2014
102 5/2/2014 11/2/2014
110 2/2/2014 5/2/2014 25/2/2014
i need a help from you to complete this task.
advance thanks
This is an old question but I'm posting this solution to counter Solution 1 which uses a CURSOR - not a recommended approach (see SQL Server DO's and DONT's[^])
The following solution uses constructs available from SQL 2005 - Common Table Expressions and Pivot. Some CP articles that may be useful on those topics:
Common Table Expressions(CTE) in SQL SERVER 2008[^]
Simple Way To Use Pivot In SQL Query[^]
My sample data:create table t1 ( code int, dt date ) INSERT t1 values (101,'12-FEB-2014'), (101,'13-FEB-2014'), (101,'17-FEB-2014'), (102,'5-FEB-2014'), (102,'11-FEB-2014'), (110,'2-FEB-2014'), (110,'5-FEB-2014'), (110,'25-FEB-2014');The query
WITH CTE1(code, dt, datenumber) AS ( SELECT [code],dt, 'dt' + CAST(ROW_NUMBER() over(PARTITION BY code ORDER BY code, dt ASC) AS VARCHAR) datenumber FROM T1 ) select code, dt, datenumber into ##t2 from CTE1 SELECT * FROM ( SELECT code, dt, datenumber FROM ##t2 ) as s PIVOT ( MAX(dt) FOR [datenumber] IN ([dt1], [dt2], [dt3], [dt4]) )AS pResults:
Code dt1 dt2 dt3 dt4 101 2014-02-12 2014-02-13 2014-02-17 NULL 102 2014-02-05 2014-02-11 NULL NULL 110 2014-02-02 2014-02-05 2014-02-25 NULL
这篇关于行从一个表到另一个表的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!