22,210
社区成员
发帖
与我相关
我的任务
分享求一行转列语句
--测试数据
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移动到最前,然后查出离当天时间最近(比当天时间大)的数据并将做的工作分配到各对应人
效果如下
...全文
请发表友善的回复…
发表回复
htl258_Tony 2010-06-10
- 打赏
- 举报
--测试数据
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
')
/*
李四 挖土
麻三 修剪
麻子
王二 浇水
王一
张三
*/
水哥阿乐 2010-06-10
- 打赏
- 举报
现在有个问题，就是如果把这第一行数据如
'1','张三','挖地','2010-5-29'
把挖地改成挖土，结果变成了如下
李四 挖土
麻三 修剪
麻子
王二 浇水
王一
张三 挖土
这个比当天日期小的数据张三挖土也出来了,帮忙解决一下，我等会再开一贴加送100分你
'1','张三','挖地','2010-5-29'
把挖地改成挖土，结果变成了如下
李四 挖土
麻三 修剪
麻子
王二 浇水
王一
张三 挖土
这个比当天日期小的数据张三挖土也出来了,帮忙解决一下，我等会再开一贴加送100分你
htl258_Tony 2010-06-10
- 打赏
- 举报
--那应该是这个结果才对啊
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
李四 挖土
麻三 修剪
麻子
王二 浇水
王一
张三
*/水哥阿乐 2010-06-10
- 打赏
- 举报
还是第二个问题没有对应好，应该是比当天日期大且相近
PersonName 1 2 3
张三
李四 挖土
王二 浇水
麻子
王一
麻三 修剪
PersonName 1 2 3
张三
李四 挖土
王二 浇水
麻子
王一
麻三 修剪
htl258_Tony 2010-06-09
- 打赏
- 举报
[Quote=引用 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……
[/Quote]
引用 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……
[/Quote]
sp_dbcmptlevel 库名,90htl258_Tony 2010-06-09
- 打赏
- 举报
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
李四
麻三
麻子
王二 浇水
王一 采摘
张三 挖地
*/水哥阿乐 2010-06-09
- 打赏
- 举报
我用的是SQL2005
水哥阿乐 2010-06-09
- 打赏
- 举报
[Quote=引用 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……
[/Quote]
消息 325，级别 15，状态 1，第 6 行
'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
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……
[/Quote]
消息 325，级别 15，状态 1，第 6 行
'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
水哥阿乐 2010-06-09
- 打赏
- 举报
先谢谢二位行转列是实现了，帮忙过滤一下要日期相近并大于当天的数据
GOODlivelife 2010-06-09
- 打赏
- 举报
好像没考虑到第二个条件
htl258_Tony 2010-06-09
- 打赏
- 举报
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
李四 挖土
麻三 修剪
麻子 松土
王二 浇水
王一 采摘
张三 挖地
*/水哥阿乐 2010-06-09
- 打赏
- 举报
与我要求的效果不符啊，只要1，2，3列,另外日期数据要对应比当天时期大且相近
GOODlivelife 2010-06-09
- 打赏
- 举报
[Quote=引用 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……
[/Quote]
不好意思，弄错了一个字段，这是动态的
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……
[/Quote]
不好意思，弄错了一个字段，这是动态的
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 行受影响)
**/GOODlivelife 2010-06-09
- 打赏
- 举报
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 行受影响)
**/水哥阿乐 2010-06-09
- 打赏
- 举报
数据是动态的。数据不能确定由别人添加的,这只是举个例子
GOODlivelife 2010-06-09
- 打赏
- 举报
1 2 3是固定的吗？
