sql行转列问题!!!!!! 在线等.

[sky3333ss]

表结构(数据):
PersonID INTime IsIN
1 2016-09-07 08:12:00 1
1 2016-09-07 17:42:00 0
1 2016-09-08 09:11:00 1
1 2016-09-08 18:02:00 0
1 2016-09-08 19:12:00 0

想要的结果为:
PersonID date mintime maxtime
1 2016-09-07 2016-09-07 08:12:00 2016-09-07 17:42:00
1 2016-09-08 2016-09-08 09:11:00 2016-09-08 19:12:00

把每人每天的最大值和最小值取出来

[mingqing6364]

;WITH T (PersonID,INTime,IsIN) AS
(
	SELECT 1,'2016-09-07 08:12:00',1 UNION ALL
	SELECT 1,'2016-09-07 17:42:00',0 UNION ALL
	SELECT 1,'2016-09-08 09:11:00',1 UNION ALL
	SELECT 1,'2016-09-08 18:02:00',0 UNION ALL
	SELECT 1,'2016-09-08 19:12:00',0
)
SELECT
	PersonID,
	CONVERT(CHAR(10),INTime,120) AS [date],
	MAX(INTime) AS maxtime,
	MIN(INTime) AS mintime
FROM T
GROUP BY
	PersonID,
	CONVERT(CHAR(10),INTime,120)

[中国风]

SELECT  PersonID ,
        CONVERT(VARCHAR(10), INTime, 120) AS INTime ,
        MAX(CASE WHEN IsIN = 1 THEN INTime
            END) AS MinINTime ,
        MAX(CASE WHEN IsIN = 0 THEN INTime
            END) AS MaxINTime
FROM    TableName
GROUP BY PersonID ,
        CONVERT(VARCHAR(10), INTime, 120)

[中国风]

IsIN=1时每天有1..n时用min

SELECT  PersonID ,
        CAST(INTime AS Date) AS Date ,
        MIN(CASE WHEN IsIN = 1 THEN INTime
            END) AS MinINTime ,
        MAX(CASE WHEN IsIN = 0 THEN INTime
            END) AS MaxINTime
FROM    TableName
GROUP BY PersonID ,
         CAST(INTime AS Date)

[sky3333ss]

谢谢了!!!!!!! 还是论坛高手多, 我要好好学习天天向上啊