-- 简单的转置, 在SQL 2005中实现的方法是:-- 测试数据
CREATE TABLE tb(name varchar(10), date datetime)
INSERT tb SELECT 1, '1999-01-01 08:10:00.000'
UNION ALL SELECT 1, '1999-01-01 12:00:00.000'
UNION ALL SELECT 1, '1999-01-01 13:10:00.000'
UNION ALL SELECT 1, '1999-01-01 17:30:00.000'
UNION ALL SELECT 1, '1999-01-01 18:20:00.000'
UNION ALL SELECT 1, '1999-01-01 21:30:00.000'
UNION ALL SELECT 2, '1999-01-01 08:10:00.000'
UNION ALL SELECT 2, '1999-01-01 12:00:00.000'
UNION ALL SELECT 2, '1999-01-01 17:30:00.000'
UNION ALL SELECT 2, '1999-01-01 13:10:00.000'-- 转置处理
DECLARE @i int, @fd1 nvarchar(max), @fd2 nvarchar(max)
SELECT TOP 1
@fd1 = '', @fd2 = '', @i = COUNT(*)
FROM tb
GROUP BY name
ORDER BY COUNT(*) DESC
WHILE @i > 0
SELECT
@fd1 = ',' + QUOTENAME(@i) + @fd1,
@fd2 = ',' + QUOTENAME('date' + RTRIM(@i)) + ' = ' + QUOTENAME(@i) + @fd2,
@i = @i -1
SET @fd1 = STUFF(@fd1, 1, 1, '')
EXEC('
SELECT name ' + @fd2 + '
FROM(
SELECT name, date, row = ROW_NUMBER() OVER(PARTITION BY name ORDER BY date)
FROM tb
)DATA
PIVOT(
MAX(date)
FOR row IN(' + @fd1 + ')
)P')DROP TABLE tb
CREATE TABLE tb(name varchar(10), date datetime)
INSERT tb SELECT 1, '1999-01-01 08:10:00.000'
UNION ALL SELECT 1, '1999-01-01 12:00:00.000'
UNION ALL SELECT 1, '1999-01-01 13:10:00.000'
UNION ALL SELECT 1, '1999-01-01 17:30:00.000'
UNION ALL SELECT 1, '1999-01-01 18:20:00.000'
UNION ALL SELECT 1, '1999-01-01 21:30:00.000'
UNION ALL SELECT 2, '1999-01-01 08:10:00.000'
UNION ALL SELECT 2, '1999-01-01 12:00:00.000'
UNION ALL SELECT 2, '1999-01-01 17:30:00.000'
UNION ALL SELECT 2, '1999-01-01 13:10:00.000'-- 转置处理
DECLARE @i int, @fd1 nvarchar(max), @fd2 nvarchar(max)
SELECT TOP 1
@fd1 = '', @fd2 = '', @i = COUNT(*)
FROM tb
GROUP BY name
ORDER BY COUNT(*) DESC
WHILE @i > 0
SELECT
@fd1 = ',' + QUOTENAME(@i) + @fd1,
@fd2 = ',' + QUOTENAME('date' + RTRIM(@i)) + ' = ' + QUOTENAME(@i) + @fd2,
@i = @i -1
SET @fd1 = STUFF(@fd1, 1, 1, '')
EXEC('
SELECT name ' + @fd2 + '
FROM(
SELECT name, date, row = ROW_NUMBER() OVER(PARTITION BY name ORDER BY date)
FROM tb
)DATA
PIVOT(
MAX(date)
FOR row IN(' + @fd1 + ')
)P')DROP TABLE tb
-- 测试数据
CREATE TABLE tb(name varchar(10), date datetime)
INSERT tb SELECT 1, '1999-01-01 08:10:00.000'
UNION ALL SELECT 1, '1999-01-01 12:00:00.000'
UNION ALL SELECT 1, '1999-01-01 13:10:00.000'
UNION ALL SELECT 1, '1999-01-01 17:30:00.000'
UNION ALL SELECT 1, '1999-01-01 18:20:00.000'
UNION ALL SELECT 1, '1999-01-01 21:30:00.000'
UNION ALL SELECT 2, '1999-01-01 08:10:00.000'
UNION ALL SELECT 2, '1999-01-01 12:00:00.000'
UNION ALL SELECT 2, '1999-01-01 17:30:00.000'
UNION ALL SELECT 2, '1999-01-01 13:10:00.000'-- 转置处理
DECLARE @i int, @fd nvarchar(4000)
SELECT TOP 1
@fd = '', @i = COUNT(*)
FROM tb
GROUP BY name
ORDER BY COUNT(*) DESC
WHILE @i > 0
SELECT
@fd = ',' + QUOTENAME('date' + RTRIM(@i))
+ '=MAX(CASE row WHEN ' + RTRIM(@i)
+ ' THEN date END)'
+ @fd,
@i = @i -1
EXEC('
SELECT name ' + @fd + '
FROM(
SELECT name, date, row = (SELECT COUNT(*) FROM tb WHERE name = A.name AND date <= a.date)
FROM tb A
)DATA
GROUP BY name')DROP TABLE tb
3 1999-01-01 17:30:00.000
3 1999-01-01 13:10:00.000那结果应该是什么? 第3条记录满足条件, 是否就是变成:
3 1999-01-01 12:00:00.000 1999-01-01 17:30:00.000 1999-01-01 13:10:00.000再比如:
3 1999-01-01 12:00:00.000
3 1999-01-01 13:10:00.000
是否结果就是:
3 1999-01-01 12:00:00.000 NULL 1999-01-01 13:10:00.000而对于
3 1999-01-01 17:00:00.000
3 1999-01-01 17:30:00.000
3 1999-01-01 13:10:00.000
结果就是:
3 1999-01-01 17:00:00.000 1999-01-01 17:30:00.000 1999-01-01 13:10:00.000
3 1999-01-01 12:00:00.000
3 1999-01-01 17:30:00.000
3 1999-01-01 13:10:00.000结果应该是
3 null 1999-01-01 12:00:00 1999-01-01 13:10:00 1999-01-01 17:30:00 null null null
比如:
3 1999-01-01 12:00:00.000
3 1999-01-01 13:10:00.000 结果应该是
3 null 1999-01-01 12:00:00.000 1999-01-01 13:10:00.000 NULL NULL NULL再次感谢邹老大的答案,如果这种不能实现,或比较麻烦,我考虑其他方法
CREATE TABLE tb(name varchar(10), date datetime)
INSERT tb SELECT 1, '1999-01-01 08:10:00.000'
UNION ALL SELECT 1, '1999-01-01 12:00:00.000'
UNION ALL SELECT 1, '1999-01-01 13:10:00.000'
UNION ALL SELECT 1, '1999-01-01 17:30:00.000'
UNION ALL SELECT 1, '1999-01-01 18:20:00.000'
UNION ALL SELECT 1, '1999-01-01 21:30:00.000'
UNION ALL SELECT 2, '1999-01-01 08:10:00.000'
UNION ALL SELECT 2, '1999-01-01 12:00:00.000'
UNION ALL SELECT 2, '1999-01-01 17:30:00.000'
UNION ALL SELECT 2, '1999-01-01 13:10:00.000'
UNION ALL SELECT 3, '1999-01-01 12:00:00.000'
UNION ALL SELECT 3, '1999-01-01 17:30:00.000'
UNION ALL SELECT 3, '1999-01-01 13:10:00.000'
UNION ALL SELECT 4, '1999-01-01 13:10:00.000'-- 转置处理
DECLARE @i int, @fd1 nvarchar(max), @fd2 nvarchar(max)
SELECT TOP 1
@fd1 = '', @fd2 = '', @i = COUNT(*)
FROM tb
GROUP BY name
ORDER BY COUNT(*) DESC
WHILE @i > 0
SELECT
@fd1 = ',' + QUOTENAME(@i) + @fd1,
@fd2 = ',' + QUOTENAME('date' + RTRIM(@i)) + ' = ' + QUOTENAME(@i) + @fd2,
@i = @i -1
SET @fd1 = STUFF(@fd1, 1, 1, '')
EXEC('
SELECT name ' + @fd2 + '
FROM(
SELECT name, date, row = ROW_NUMBER() OVER(PARTITION BY name ORDER BY date) + ISNULL(2- B.cnt, 0)
FROM tb A
OUTER APPLY(
SELECT cnt = COUNT(*) FROM tb
WHERE name = A.name
AND CONVERT(char(5), date, 108) < ''13:00''
HAVING COUNT(*) < 2
)B
)DATA
PIVOT(
MAX(date)
FOR row IN(' + @fd1 + ')
)P')DROP TABLE tb
-- 测试数据
CREATE TABLE tb(name varchar(10), date datetime)
INSERT tb SELECT 1, '1999-01-01 08:10:00.000'
UNION ALL SELECT 1, '1999-01-01 12:00:00.000'
UNION ALL SELECT 1, '1999-01-01 13:10:00.000'
UNION ALL SELECT 1, '1999-01-01 17:30:00.000'
UNION ALL SELECT 1, '1999-01-01 18:20:00.000'
UNION ALL SELECT 1, '1999-01-01 21:30:00.000'
UNION ALL SELECT 2, '1999-01-01 08:10:00.000'
UNION ALL SELECT 2, '1999-01-01 12:00:00.000'
UNION ALL SELECT 2, '1999-01-01 17:30:00.000'
UNION ALL SELECT 2, '1999-01-01 13:10:00.000'
UNION ALL SELECT 3, '1999-01-01 12:00:00.000'
UNION ALL SELECT 3, '1999-01-01 17:30:00.000'
UNION ALL SELECT 3, '1999-01-01 13:10:00.000'
UNION ALL SELECT 4, '1999-01-01 13:10:00.000'-- 转置处理
DECLARE @i int, @fd nvarchar(4000)
SELECT TOP 1
@fd = '', @i = COUNT(*)
FROM tb
GROUP BY name
ORDER BY COUNT(*) DESC
WHILE @i > 0
SELECT
@fd = ',' + QUOTENAME('date' + RTRIM(@i))
+ '=MAX(CASE row WHEN ' + RTRIM(@i)
+ ' THEN date END)'
+ @fd,
@i = @i -1
EXEC('
SELECT name ' + @fd + '
FROM(
SELECT name, date,
row = (SELECT COUNT(*) FROM tb WHERE name = A.name AND date <= a.date)
+ ISNULL(2 - (
SELECT cnt = COUNT(*) FROM tb
WHERE name = A.name
AND CONVERT(char(5), date, 108) < ''13:00''
HAVING COUNT(*) < 2 ), 0)
FROM tb A
)DATA
GROUP BY name')DROP TABLE tb