求SQL语句将多条数据组织成一条数据

Question

表结构与数据记录如下：
record_id   employee_id   device_id   put_date
1 63 32 2011-04-21 08:30:00
2 33 32 2011-04-21 08:30:00
3 63 32 2011-04-21 12:00:00
4 33 32 2011-04-21 12:00:00
5 63 32 2011-04-21 13:30:00
6 33 32 2011-04-21 13:30:00
7 63 32 2011-04-21 18:00:00
8 33 32 2011-04-21 18:00:00
9 63 32 2011-04-22 08:30:00
10 63 32 2011-04-22 12:00:00
11 63 32 2011-04-22 13:30:00
12 63 32 2011-04-22 18:00:00求SQL查询语句使其根据上表得到查询结构如下（数据规则：一个员工每天有四条有效打卡记录，将每个员工一天的打卡记录组织成一条记录数据）
employee_id   device_id   put_date   put_date_one   put_date_two   put_date_three   put_date_four
63   32   2011-04-21   2011-04-21 08:30:00   2011-04-21 12:00:00   2011-04-21 13:30:00   2011-04-21 18:00:00
33   32   2011-04-21   2011-04-21 08:30:00   2011-04-21 12:00:00   2011-04-21 13:30:00   2011-04-21 18:00:00
63   32   2011-04-22   2011-04-22 08:30:00   2011-04-22 12:00:00   2011-04-22 13:30:00   2011-04-22 18:00:00

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-04-25 16:07:33
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([record_id] int,[employee_id] int,[device_id] int,[put_date] datetime)
insert [tb]
select 1,63,32,'2011-04-21 08:30:00' union all
select 2,33,32,'2011-04-21 08:30:00' union all
select 3,63,32,'2011-04-21 12:00:00' union all
select 4,33,32,'2011-04-21 12:00:00' union all
select 5,63,32,'2011-04-21 13:30:00' union all
select 6,33,32,'2011-04-21 13:30:00' union all
select 7,63,32,'2011-04-21 18:00:00' union all
select 8,33,32,'2011-04-21 18:00:00' union all
select 9,63,32,'2011-04-22 08:30:00' union all
select 10,63,32,'2011-04-22 12:00:00' union all
select 11,63,32,'2011-04-22 13:30:00' union all
select 12,63,32,'2011-04-22 18:00:00'
--------------开始查询--------------------------
select
   employee_id,
   device_id,
   CONVERT(varchar(10),put_date,120) as put_date,
   MAX(case id when 1 then put_date else '' end) as put_date_one ,
   MAX(case id when 2 then put_date else '' end) as put_date_two ,
   MAX(case id when 3 then put_date else '' end) as put_date_three ,
   MAX(case id when 4 then put_date else '' end) as put_date_four 
from
   (select ID=ROW_NUMBER()over(PARTITION by employee_id order by getdate()),* from tb)t
group by
   employee_id,
   device_id,
   CONVERT(varchar(10),put_date,120)
----------------结果----------------------------
/* employee_id device_id   put_date   put_date_one            put_date_two            put_date_three          put_date_four
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
33          32          2011-04-21 2011-04-21 08:30:00.000 2011-04-21 12:00:00.000 2011-04-21 18:00:00.000 2011-04-21 13:30:00.000
63          32          2011-04-21 2011-04-21 18:00:00.000 2011-04-21 08:30:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
63          32          2011-04-22 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 2011-04-22 08:30:00.000 2011-04-22 12:0
*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-04-25 16:07:33
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([record_id] int,[employee_id] int,[device_id] int,[put_date] datetime)
insert [tb]
select 1,63,32,'2011-04-21 08:30:00' union all
select 2,33,32,'2011-04-21 08:30:00' union all
select 3,63,32,'2011-04-21 12:00:00' union all
select 4,33,32,'2011-04-21 12:00:00' union all
select 5,63,32,'2011-04-21 13:30:00' union all
select 6,33,32,'2011-04-21 13:30:00' union all
select 7,63,32,'2011-04-21 18:00:00' union all
select 8,33,32,'2011-04-21 18:00:00' union all
select 9,63,32,'2011-04-22 08:30:00' union all
select 10,63,32,'2011-04-22 12:00:00' union all
select 11,63,32,'2011-04-22 13:30:00' union all
select 12,63,32,'2011-04-22 18:00:00'
--------------开始查询--------------------------
select
   employee_id,
   device_id,
   CONVERT(varchar(10),put_date,120) as put_date,
   MAX(case id when 1 then put_date else '' end) as put_date_one ,
   MAX(case id when 2 then put_date else '' end) as put_date_two ,
   MAX(case id when 3 then put_date else '' end) as put_date_three ,
   MAX(case id when 4 then put_date else '' end) as put_date_four 
from
   (select ID=ROW_NUMBER()over(PARTITION by employee_id,device_id,CONVERT(varchar(10),put_date,120) order by getdate()),* from tb)t
group by
   employee_id,
   device_id,
   CONVERT(varchar(10),put_date,120)
----------------结果----------------------------
/*employee_id device_id   put_date   put_date_one            put_date_two            put_date_three          put_date_four
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
33          32          2011-04-21 2011-04-21 08:30:00.000 2011-04-21 12:00:00.000 2011-04-21 18:00:00.000 2011-04-21 13:30:00.000
63          32          2011-04-21 2011-04-21 18:00:00.000 2011-04-21 08:30:00.000 2011-04-21 13:30:00.000 2011-04-21 12:00:00.000
63          32          2011-04-22 2011-04-22 08:30:00.000 2011-04-22 12:00:00.000 2011-04-22 13:30:00.000 2011-04-22 18:00:00.0000 2011-04-22 12:0
*/

Answer 3

---终于对了
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-04-25 16:07:33
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
-- Jul  9 2008 14:43:34 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([record_id] int,[employee_id] int,[device_id] int,[put_date] datetime)
insert [tb]
select 1,63,32,'2011-04-21 08:30:00' union all
select 2,33,32,'2011-04-21 08:30:00' union all
select 3,63,32,'2011-04-21 12:00:00' union all
select 4,33,32,'2011-04-21 12:00:00' union all
select 5,63,32,'2011-04-21 13:30:00' union all
select 6,33,32,'2011-04-21 13:30:00' union all
select 7,63,32,'2011-04-21 18:00:00' union all
select 8,33,32,'2011-04-21 18:00:00' union all
select 9,63,32,'2011-04-22 08:30:00' union all
select 10,63,32,'2011-04-22 12:00:00' union all
select 11,63,32,'2011-04-22 13:30:00' union all
select 12,63,32,'2011-04-22 18:00:00'
--------------开始查询--------------------------
select
   employee_id,
   device_id,
   CONVERT(varchar(10),put_date,120) as put_date,
   MAX(case id when 1 then put_date else '' end) as put_date_one ,
   MAX(case id when 2 then put_date else '' end) as put_date_two ,
   MAX(case id when 3 then put_date else '' end) as put_date_three ,
   MAX(case id when 4 then put_date else '' end) as put_date_four 
from
   (select ID=ROW_NUMBER()over(PARTITION by employee_id,device_id,CONVERT(varchar(10),put_date,120) order by put_date),* from tb)t
group by
   employee_id,
   device_id,
   CONVERT(varchar(10),put_date,120)
----------------结果----------------------------
/*employee_id device_id   put_date   put_date_one            put_date_two            put_date_three          put_date_four
----------- ----------- ---------- ----------------------- ----------------------- ----------------------- -----------------------
33          32          2011-04-21 2011-04-21 08:30:00.000 2011-04-21 12:00:00.000 2011-04-21 13:30:00.000 2011-04-21 18:00:00.000
63          32          2011-04-21 2011-04-21 08:30:00.000 2011-04-21 12:00:00.000 2011-04-21 13:30:00.000 2011-04-21 18:00:00.000
63          32          2011-04-22 2011-04-22 08:30:00.000 2011-04-22 12:00:00.000 2011-04-22 13:30:00.000 2011-04-22 18:00:00.000
*/

Answer 4

USE TEST 
GO --1: Create Test table :
IF  OBJECT_ID ('tempdb.dbo.#Test')IS NOT NULL 
DROP TABLE #TestGO CREATE TABLE #Test 
(
[record_id]    INT IDENTITY (1,1),
[employee_id]  INT,
[device_id]    INT,
[put_date]     DATETIME
)INSERT #Test 
SELECT 63,32,'2011-04-21 08:30:00' UNION ALL
SELECT 33,32,'2011-04-21 08:30:00' UNION ALL
SELECT 63,32,'2011-04-21 12:00:00' UNION ALL
SELECT 33,32,'2011-04-21 12:00:00' UNION ALL
SELECT 63,32,'2011-04-21 13:30:00' UNION ALL
SELECT 33,32,'2011-04-21 13:30:00' UNION ALL
SELECT 63,32,'2011-04-21 18:00:00' UNION ALL
SELECT 33,32,'2011-04-21 18:00:00' UNION ALL
SELECT 63,32,'2011-04-22 08:30:00' UNION ALL
SELECT 63,32,'2011-04-22 12:00:00' UNION ALL
SELECT 63,32,'2011-04-22 13:30:00' UNION ALL
SELECT 63,32,'2011-04-22 18:00:00'
WITH test AS 
(
SELECT   [employee_id],
         [device_id],
         [put_date]=CONVERT(CHAR(10),[put_date],120),
         [put_date]AS [put_dateOriginal],
         [ROW_NUMBER_Date] = ROW_NUMBER() OVER(PARTITION BY [employee_id],[device_id],CONVERT(CHAR(10),[put_date],120) ORDER BY [put_date])
FROM     #Test 
) 
SELECT [employee_id],[device_id],[put_date],
       ISNULL(MAX(CASE WHEN [ROW_NUMBER_Date]=1 THEN [put_dateOriginal] END),0)AS [put_date_one],
       ISNULL(MAX(CASE WHEN [ROW_NUMBER_Date]=2 THEN [put_dateOriginal] END),0)AS [put_date_two],
       ISNULL(MAX(CASE WHEN [ROW_NUMBER_Date]=3 THEN [put_dateOriginal] END),0)AS [put_date_Three],
       ISNULL(MAX(CASE WHEN [ROW_NUMBER_Date]=4 THEN [put_dateOriginal] END),0)AS [put_date_Four]
FROM   test
GROUP BY [employee_id],[device_id],[put_date]