在sqlserver中n行到n / 2列 [英] n rows to n/2 colums in sqlserver
本文介绍了在sqlserver中n行到n / 2列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的桌子
insert < span class =code-keyword> into attn(StaffNo,ADate,ATime) values (' 12345',' 12-02-2013 ', 09 。 10 )
插入 进入 attn(StaffNo,ADate,ATime)值(' 12345',' 12-02-2013', 10 。 03 )
insert into attn(StaffNo,ADate,ATime) values (< span class =code-string>' 12345',' 12-02-2013', 10 。 40 )
插入 进入 attn(StaffNo,ADate,ATime) values (' 12345',' 12-02-2013', 17 。 30 )
我想要这种格式
OutPut:
StaffNo ADate InTime OutTime< br />
< br />
12333,12-02-2013 09.10 10.03< br />
12333 ,12-02-2013 10.40 17.30
解决方案
您可以浏览以下代码块
DECLARE @ attn TABLE (StaffNo INT ,ADate VARCHAR ( 20 ),ATime VARCHAR ( 20 ));
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES (' 12345',' 12-02-2013', 09 。 10 );
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES (' 12345' ,' 12-02-2013', 10 。 03 );
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES (' 12345' ,' 12-02-2013', 10 。 40 );
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES (' 12345' ,' 12-02-2013', 17 。 30 );
WITH tmp AS (
SELECT ROW_NUMBER() OVER ( ORDER BY StaffNo,ADate) AS RowNum,* FROM @ attn
)
select tmp1.StaffNo,tmp1.ADate,tmp1.ATime InTime,tmp2.ATime OutTime
FROM tmp as tmp1,tmp as tmp2 WHERE tmp1.RowNum = tmp2.RowNum - 1
AND tmp2.RowNum% 2 = 0 AND tmp1.StaffNo = tmp2.StaffNo
谢谢你。
你走了!
DECLARE @ attn TABLE (StaffNo INT ,添加 VARCHAR ( 20 ),ATime VARCHAR ( 20 ))
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES ( ' 12345',' 12-02-2013', 09 。 10 )
INSERT INTO @ attn (StaffNo,ADate, ATime) VALUES (' 12345', ' 12-02-2013', 10 。 03 )
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES (' 12345',' 12- 02-2013', 10 。 40 )
INSERT INTO @ attn (StaffNo,ADate,ATime) VALUES (' 12345','跨度>12-02-2013', 17 。 30 )
SELECT StaffNo,
ADate,
ATime
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY StaffNo) AS RowNum,*
FROM @ attn
) AS t
WHERE t.RowNum% 2 = 0 跨度>
I am having the table like this
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 09.10)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.03)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 10.40)
insert into attn (StaffNo, ADate, ATime) values ('12345', '12-02-2013', 17.30)
I want in this format
OutPut :
StaffNo ADate InTime OutTime <br /> <br /> 12333, 12-02-2013 09.10 10.03<br /> 12333, 12-02-2013 10.40 17.30
解决方案
Hi go through the following code block
DECLARE @attn TABLE (StaffNo INT, ADate VARCHAR(20), ATime VARCHAR(20)); INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 09.10); INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.03); INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.40); INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 17.30); WITH tmp AS ( SELECT ROW_NUMBER() OVER(ORDER BY StaffNo,ADate) AS RowNum,* FROM @attn ) select tmp1.StaffNo,tmp1.ADate,tmp1.ATime InTime,tmp2.ATime OutTime FROM tmp as tmp1,tmp as tmp2 WHERE tmp1.RowNum = tmp2.RowNum - 1 AND tmp2.RowNum % 2 = 0 AND tmp1.StaffNo = tmp2.StaffNo
Thank you.
Here you go!
DECLARE @attn TABLE (StaffNo INT, ADate VARCHAR(20), ATime VARCHAR(20)) INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 09.10) INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.03) INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.40) INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 17.30) SELECT StaffNo, ADate, ATime FROM ( SELECT ROW_NUMBER() OVER(ORDER BY StaffNo) AS RowNum,* FROM @attn ) AS t WHERE t.RowNum % 2 = 0
这篇关于在sqlserver中n行到n / 2列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文