如何在sql server 2008中将行转换为列 [英] how to convert rows into columns in sql server 2008

问题描述

我很难搞清楚这个请帮帮我我有时间进/出sql查询我有一张表如下所示。有四列显示时间输入/输出信息，例如...

I am having tough time to figure out this please help me I have time in/out sql query I have a table looks like below. there are four columns display time in/out info such as...

Date Day    Day  TimeStamp CheckType   
10/11/2014  Sat  8:30 am    in
10/11/2014  Sat  11:30am    out
10/11/2014  Sat  1:30pm     in
10/11/2014  Sat             out
10/12/2014  Sun   9:00am     in
10/12/2014  Sun  11:20pm    out
10/12/2014  Sun  5:20pm     out
10/13/2014  Mon  8:00am      in
10/13/2014  Mon  6:10pm      in

所以无论谁签入或结账，记录都会按顺序显示结果，如果有人应该退房，但意外按下按钮然后这将显示为（in）或如果有人忘记退房，那么该空间将显示空白



我我试图将行转换为列并在下面显示此类信息

so whoever checkin or checkout then the record will display the result in order and if someone is supposed to check out but accidently pressed in button then this will display as it is (in) or if someone forget to check out then that space will show blank

I am trying to convert rows into column and display such information in below

Date       Day   Time  Type Time    Type Time    Type   Time Type etc-----
10/11/2014 Sat     8:30am in 11:30am  out 1:30pm  in     
10/12/2014 Sun      9:00am in 11:20am  out 1:20pm  in  6:20pm in
10/13/2014 Mon      8:00am in 6:10pm   out
10/14/2014 Tus      8:20am in

等





我试图使用pivot

etc


I have tried to use pivot

select Date, Day, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc---
from
(
select Date, Day, Stamptime, CheckTime, userID
from a table
)
pivot
(
    max(StampTime)
    for stamptime in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc---)
) as PivotTable

任何人都可以解释如何将行转换为我已经花了很多天的列。

can anyone explain how to convert the rows into columns I have spent many days already.

推荐答案

请看看我过去的答案：

豪w创建数据透视表 [ ^ ]

动态创建基于另一个表的表？ [ ^ ]

在审计系统中使用的SQL查询中需要帮助 [ ^ ]



下次，请使用改善问题窗口小部件，如果有人要求您提供更多详细信息。

Please, see my past answers:
how to create Pivot table[^]
Dynamic Create table based on another Table ?[^]
Need help in SQL query used in attandance system[^]

Next time, please use "Improve question" widget and provide more details if somebody ask you to do that.

这篇关于如何在sql server 2008中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！