求一行转列语句

--测试数据
CREATE TABLE Tasks
	(ID bigint IDENTITY(1,1),
	ProjectID nvarchar(255),
	PersonName nvarchar(255),
	CurrentWorking nvarchar(50),
    TaskDate nvarchar(50))

insert tasks(ProjectID,Personname,currentWorking,taskdate) values('1','张三','挖地','2010-5-29')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('1','李四','挖土','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('2','王二','浇水','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('2','麻子','松土','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('3','王一','采摘','2010-6-3')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('3','麻三','修剪','2010-7-9')

1想实现行转列,例如有1,1,2,2,3,3忽略重复就变成1,2,3列
2将personname移动到最前,然后查出离当天时间最近(比当天时间大)的数据并将做的工作分配到各对应人

效果如下

16 个解决方案

#1

1 2 3是固定的吗？

#2

数据是动态的。数据不能确定由别人添加的,这只是举个例子

#3

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when id='+ltrim(id)+' then CurrentWorking else '''' end) as ['+ltrim(id)+']'
from(select distinct id from tasks) t
exec('select PersonName,'+@sql+' from tasks group by PersonName')
/**
PersonName           1                                                  2                                                  3                                                  4                                                  5                                                  6
-------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
李四                                                                      挖土                                                                                                                                                                                                          
麻三                                                                                                                                                                                                                                                                                  修剪
麻子                                                                                                                                                                            松土                                                                                                    
王二                                                                                                                         浇水                                                                                                                                                       
王一                                                                                                                                                                                                                               采摘                                                 
张三                   挖地                                                                                                                                                                                                                                                             

(6 行受影响)

**/

#4

引用 3 楼 goodlivelife 的回复:

SQL code
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when id='+ltrim(id)+' then CurrentWorking else '''' end) as ['+ltrim(id)+']'
from(select distinct id from tasks) t
e……

不好意思，弄错了一个字段，这是动态的

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when id='+ltrim(ProjectID)+' then CurrentWorking else '''' end) as ['+ltrim(ProjectID)+']'
from(select distinct ProjectID from tasks) t
exec('select PersonName,'+@sql+' from tasks group by PersonName')
/**
PersonName           1          2          3
-------------------- ---------- ---------- ----------
李四                              挖土         
麻三                                         
麻子                                         
王二                                         浇水
王一                                         
张三                   挖地                    

(6 行受影响)

**/

#5

与我要求的效果不符啊，只要1，2，3列,另外日期数据要对应比当天时期大且相近

#6

DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectID),
	@s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME(ProjectID)+','''')'+QUOTENAME(ProjectID)
FROM Tasks 
GROUP BY ProjectID
EXEC('
	SELECT PersonName,'+@s1+'
	FROM (
			SELECT ProjectID, PersonName, CurrentWorking 
			FROM Tasks 
		) a
		PIVOT (MAX(CurrentWorking) FOR ProjectID IN('+@s+')) b
')
/*
PersonName	1	2	3
李四	挖土		
麻三			修剪
麻子		松土	
王二		浇水	
王一			采摘
张三	挖地		
*/

#7

好像没考虑到第二个条件

#8

先谢谢二位行转列是实现了，帮忙过滤一下要日期相近并大于当天的数据

#9

引用 6 楼 htl258 的回复:

SQL code
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectID),
    @s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME(ProjectID)+','''')'+QUOTENAME(ProjectID)
FROM Tas……

消息 325，级别 15，状态 1，第 6 行
'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。

#10

我用的是SQL2005

#11

DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectID),
	@s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME(ProjectID)+','''')'+QUOTENAME(ProjectID)
FROM Tasks 
GROUP BY ProjectID
EXEC('
	SELECT PersonName,'+@s1+'
	FROM (
			SELECT ProjectID, PersonName, 
				CASE CurrentWorking
                    WHEN (
                             SELECT TOP 1 CurrentWorking 
                             FROM Tasks
                             WHERE ProjectID = t.ProjectID
                             ORDER BY ABS(DATEDIFF(dd, GETDATE(), TaskDate))
                         ) THEN CurrentWorking
                    ELSE ''''
                END CurrentWorking 
			FROM Tasks t
		) a
		PIVOT (MAX(CurrentWorking) FOR ProjectID IN('+@s+')) b
')
/*
PersonName	1	2	3
李四			
麻三			
麻子			
王二		浇水	
王一			采摘
张三	挖地		

*/

#12

引用 9 楼 wtuqi 的回复:

引用 6 楼 htl258 的回复:
SQL code
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectID),
@s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME(ProjectID)+','''')'+QUOTENAME(Proj……

 sp_dbcmptlevel 库名,90

#13

还是第二个问题没有对应好，应该是比当天日期大且相近

PersonName   1     2     3
张三        
李四        挖土
王二              浇水
麻子
王一                    
麻三                    修剪

#14

--那应该是这个结果才对啊

DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectID),
    @s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME(ProjectID)+','''')'+QUOTENAME(ProjectID)
FROM Tasks 
GROUP BY ProjectID
EXEC('
    SELECT PersonName,'+@s1+'
    FROM (
            SELECT ProjectID, PersonName, 
                CASE CurrentWorking
                    WHEN (
                             SELECT TOP 1 CurrentWorking 
                             FROM Tasks
                             WHERE ProjectID = t.ProjectID
                             ORDER BY CASE 
                                           WHEN TaskDate>GETDATE() THEN 0
                                           ELSE 1
                                      END, ABS(DATEDIFF(dd, GETDATE(), TaskDate))
                         ) THEN CurrentWorking
                    ELSE ''''
                END CurrentWorking 
            FROM Tasks t
        ) a
        PIVOT (MAX(CurrentWorking) FOR ProjectID IN('+@s+')) b
')
/*
PersonName	1	2	3
李四	挖土		
麻三			修剪
麻子			
王二		浇水	
王一			
张三			
*/

#15

现在有个问题，就是如果把这第一行数据如
'1','张三','挖地','2010-5-29'
把挖地改成挖土，结果变成了如下

李四 挖土
麻三 修剪
麻子
王二 浇水
王一
张三 挖土

这个比当天日期小的数据张三挖土也出来了,帮忙解决一下，我等会再开一贴加送100分你

#16

--测试数据
CREATE TABLE Tasks
    (ID bigint IDENTITY(1,1),
    ProjectID nvarchar(255),
    PersonName nvarchar(255),
    CurrentWorking nvarchar(50),
    TaskDate nvarchar(50))
    
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('1','张三','挖土','2010-5-29')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('1','李四','挖土','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('2','王二','浇水','2010-6-18')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('2','麻子','松土','2010-7-7')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('3','王一','采摘','2010-6-3')
insert tasks(ProjectID,Personname,currentWorking,taskdate) values('3','麻三','修剪','2010-7-9')


DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(ProjectID),
    @s1=ISNULL(@s1+',','')+'ISNULL('+QUOTENAME(ProjectID)+','''')'+QUOTENAME(ProjectID)
FROM Tasks 
GROUP BY ProjectID
EXEC('
    SELECT PersonName,'+@s1+'
    FROM (
            SELECT ProjectID, PersonName, 
                CASE PersonName
                    WHEN (
                             SELECT TOP 1 PersonName 
                             FROM Tasks
                             WHERE ProjectID = t.ProjectID
                             ORDER BY CASE 
                                           WHEN TaskDate>GETDATE() THEN 0
                                           ELSE 1
                                      END, ABS(DATEDIFF(dd, GETDATE(), TaskDate))
                         ) THEN CurrentWorking
                    ELSE ''''
                END CurrentWorking 
            FROM Tasks t
        ) a
        PIVOT (MAX(CurrentWorking) FOR ProjectID IN('+@s+')) b
')
/*
李四	挖土		
麻三			修剪
麻子			
王二		浇水	
王一			
张三			
*/