行到sqlserver中的列 [英] rows to columns in sqlserver

问题描述

insert into attn values(''12334'',''12-02-2013'',09.10)
insert into attn values(''12334'',''12-02-2013'',10.10)
insert into attn values(''12334'',''12-02-2013'',11.10)

insert into attn values(''32334'',''12-02-2013'',09.11)
insert into attn values(''32334'',''12-02-2013'',11.20)
insert into attn values(''32334'',''12-02-2013'',14.30)
insert into attn values(''32334'',''12-02-2013'',17.10)

insert into attn values(''32430'',''12-02-2013'',10.11)

我想要这个格式



输出：

I want in this Format

Output :

Staff_no      ADate            InTime      OutTime
'12334'     '12-02-2013'        09.10      10.10
'12334'     '12-02-2013'        11.10      -

'32334'     '12-02-2013'        09.11      11.20
'32334'     '12-02-2013'        14.30      17.10

'32430'     '12-02-2013'        10.11      -

推荐答案

见下面的查询

see the below query

create table temp (Staff_no numeric(10,0), ADate varchar(50), tme varchar(50), flag varchar(1))


insert into temp values('12334','12-02-2013',09.10,'I')
insert into temp values('12334','12-02-2013',10.10,'O')
insert into temp values('12334','12-02-2013',11.10,'I')

insert into temp values('32334','12-02-2013',09.11,'I')
insert into temp values('32334','12-02-2013',11.20,'O')
insert into temp values('32334','12-02-2013',14.30,'I')
insert into temp values('32334','12-02-2013',17.10,'O')

insert into temp values('32430','12-02-2013',10.11,'I')

Select Staff_no,ADate, isnull(case when flag='I' then tme end,'') 'Intime' ,isnull(case when flag='O' then tme end,'') 'OutTime' from temp

让我知道它是否无效。

Let me know if it is not working.

create table attn(id varchar(10), Dt datetime, InOut varchar(10));
insert into attn values('12334','12-02-2013',09.10)
insert into attn values('12334','12-02-2013',10.10)
insert into attn values('12334','12-02-2013',11.10)
 
insert into attn values('32334','12-02-2013',09.11)
insert into attn values('32334','12-02-2013',11.20)
insert into attn values('32334','12-02-2013',14.30)
insert into attn values('32334','12-02-2013',17.10) 
insert into attn values('32430','12-02-2013',10.11)
GO
Select Id, Dt, isnull(case when Status='I' then InOut end,'') 'Intime' ,isnull(case when Status='O' then InOut end,'') 'OutTime' from 
(
select * from (select row_number() over(order by id) as sl, id, Dt, Inout, 'I' as Status from attn) as t
where  (sl % 2 = 1)
union all
select * from (select row_number() over(order by id) as sl, id, Dt, Inout, 'O' as Status FROM attn) as t2 where (sl % 2 = 0)
) 
AS T

这里我假设Ist行是for和2nd for out。查询将被复杂化。您可以考虑重新设计您的表，尽管查询将尽可能简单。

Here I assume that Ist row is for in and 2nd for out. Query is going to complecated. You can think redesign your table though query will as simple as possible.

您好，



将行转换为列您需要使用PIVOT。



请通过以下链接



http://blog.sqlauthority.com/2008/06/07/sql-server-pivot- and-unpivot-table-examples [ ^ ]

这篇关于行到sqlserver中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！