declare @A table (班级 varchar(4),周一 varchar(4),周二 varchar(4),周三 varchar(4), 周四 varchar(4),周五 varchar(4),周六 varchar(4),周日 varchar(4)) insert into @A select '红班','数字',null,null,null,null,null,'体育' union all select '黄班',null,'语文','地理','化学',null,'英语',null union all select '蓝班','生物',null,null,null,'数学',null,null union all select '绿班',null,null,'政治',null,null,null,'历史';with maco as( select isnull(班级+'('+周一+')','') as col1, isnull(班级+'('+周二+')','') as col2, isnull(班级+'('+周三+')','') as col3, isnull(班级+'('+周四+')','') as col4, isnull(班级+'('+周五+')','') as col5, isnull(班级+'('+周六+')','') as col6, isnull(班级+'('+周日+')','') as col7 from @A)select a.col1 as 周一, b.col2 as 周二, c.col3 as 周三, d.col4 as 周四, e.col5 as 周五, f.col6 as 周六, g.col7 as 周七 from ( select row_number() over (order by col1) as rid,col1 from maco where col1!='') a left join (select row_number() over (order by col2) as rid,col2 from maco where col2!='') b on a.rid=b.rid left join (select row_number() over (order by col2) as rid,col3 from maco where col3!='') c on a.rid=c.rid left join (select row_number() over (order by col2) as rid,col4 from maco where col4!='') d on a.rid=d.rid left join (select row_number() over (order by col2) as rid,col5 from maco where col5!='') e on a.rid=e.rid left join (select row_number() over (order by col2) as rid,col6 from maco where col6!='') f on a.rid=f.rid left join (select row_number() over (order by col2) as rid,col7 from maco where col7!='') g on a.rid=g.rid /* 周一 周二 周三 周四 周五 周六 周七 ---------- ---------- ---------- ---------- ---------- ---------- ---------- 红班(数字) 黄班(语文) 绿班(政治) 黄班(化学) 蓝班(数学) 黄班(英语) 红班(体育) 蓝班(生物) NULL 黄班(地理) NULL NULL NULL 绿班(历史) */
CREATE TABLE #temp ( id int identity, title NVARCHAR(10), [weekday] NVARCHAR(2) ) INSERT #temp SELECT N'语文', N'周一' UNION ALL SELECT N'数学', N'周二' UNION ALL SELECT N'生物', N'周一' UNION ALL SELECT N'化学', N'周二' UNION ALL SELECT N'地理', N'周三' UNION ALL SELECT N'历史', N'周二' GO select * from #temp --SQL SELECT * from (SELECT rowno=ROW_NUMBER() OVER(PARTITION BY [weekday] ORDER BY id), title,[weekday] FROM #TEMP) a PIVOT (MAX(title) FOR [weekday] IN([周一], [周二], [周三], [周四])) b /* 1 语文 数学 地理 NULL 2 生物 化学 NULL NULL 3 NULL 历史 NULL NULL */
我刚刚有试过您的代码是OK的,但是我的表里面WEEKDAY字段是用DATENAME(dw,StartDate)出来的,并不是存在的列select * from (select rowno=ROW_NUMBER() OVER(PARTITION BY DATENAME(dw,StartDate) order by ID),AffairKeys,DATENAME(dw,StartDate) from T_A_Affair) a pivot (max(AffairKeys) for DateName(dw,StartDate) IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) b一直提示: 消息 102,级别 15,状态 1,第 4 行 '(' 附近有语法错误。
我刚刚有试过您的代码是OK的,但是我的表里面WEEKDAY字段是用DATENAME(dw,StartDate)出来的,并不是存在的列select * from (select rowno=ROW_NUMBER() OVER(PARTITION BY DATENAME(dw,StartDate) order by ID),AffairKeys,DATENAME(dw,StartDate) from T_A_Affair) a pivot (max(AffairKeys) for DateName(dw,StartDate) IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) b一直提示: 消息 102,级别 15,状态 1,第 4 行 '(' 附近有语法错误。
select max(case [weekday] when '周一' then title else '' end) as '周一', max(case [weekday] when '周二' then title else '' end) as '周二', max(case [weekday] when '周三' then title else '' end) as '周三', max(case [weekday] when '周四' then title else '' end) as '周四' from tb
use tempdb; /* create table A ( id int identity, title nvarchar(10) not null, [weekday] nvarchar(10) not null ); insert into A(title,[weekday]) values ('语文','周一'); insert into A(title,[weekday]) values ('数学','周二'); insert into A(title,[weekday]) values ('生物','周一'); insert into A(title,[weekday]) values ('化学','周二'); insert into A(title,[weekday]) values ('地理','周三'); insert into A(title,[weekday]) values ('历史','周二'); */ select MAX(case when B.[weekday] = '周一' then B.title end) as [周一], MAX(case when B.[weekday] = '周二' then B.title end) as [周二], MAX(case when B.[weekday] = '周三' then B.title end) as [周三], MAX(case when B.[weekday] = '周四' then B.title end) as [周四] from ( select *,ROW_NUMBER() over(partition by A.[weekday] order by A.id) as [sort] from A ) as B group by B.sort;
如果是lz后面说的情况 use tempdb; /* create table A ( id int identity, title nvarchar(10) not null, [date] date not null ); insert into A(title,[date]) values ('语文','2011-05-02'), ('数学','2011-05-03'), ('生物','2011-05-02'), ('化学','2011-05-03'), ('地理','2011-05-04'), ('历史','2011-05-03'); */select MAX(case when DATENAME(dw,B.[date]) = '星期一' then B.title end) as [周一], MAX(case when DATENAME(dw,B.[date]) = '星期二' then B.title end) as [周二], MAX(case when DATENAME(dw,B.[date]) = '星期三' then B.title end) as [周三], MAX(case when DATENAME(dw,B.[date]) = '星期四' then B.title end) as [周四] from ( select *,ROW_NUMBER() over(partition by DATENAME(dw,A.[date]) order by A.id) as [sort] from A ) as B group by B.sort;
declare @A table
(班级 varchar(4),周一 varchar(4),周二 varchar(4),周三 varchar(4),
周四 varchar(4),周五 varchar(4),周六 varchar(4),周日 varchar(4))
insert into @A
select '红班','数字',null,null,null,null,null,'体育' union all
select '黄班',null,'语文','地理','化学',null,'英语',null union all
select '蓝班','生物',null,null,null,'数学',null,null union all
select '绿班',null,null,'政治',null,null,null,'历史';with maco as(
select
isnull(班级+'('+周一+')','') as col1,
isnull(班级+'('+周二+')','') as col2,
isnull(班级+'('+周三+')','') as col3,
isnull(班级+'('+周四+')','') as col4,
isnull(班级+'('+周五+')','') as col5,
isnull(班级+'('+周六+')','') as col6,
isnull(班级+'('+周日+')','') as col7
from @A)select a.col1 as 周一,
b.col2 as 周二,
c.col3 as 周三,
d.col4 as 周四,
e.col5 as 周五,
f.col6 as 周六,
g.col7 as 周七
from (
select row_number() over (order by col1) as rid,col1
from maco where col1!='') a
left join (select row_number() over (order by col2) as rid,col2
from maco where col2!='') b on a.rid=b.rid
left join (select row_number() over (order by col2) as rid,col3
from maco where col3!='') c on a.rid=c.rid
left join (select row_number() over (order by col2) as rid,col4
from maco where col4!='') d on a.rid=d.rid
left join (select row_number() over (order by col2) as rid,col5
from maco where col5!='') e on a.rid=e.rid
left join (select row_number() over (order by col2) as rid,col6
from maco where col6!='') f on a.rid=f.rid
left join (select row_number() over (order by col2) as rid,col7
from maco where col7!='') g on a.rid=g.rid
/*
周一 周二 周三 周四 周五 周六 周七
---------- ---------- ---------- ---------- ---------- ---------- ----------
红班(数字) 黄班(语文) 绿班(政治) 黄班(化学) 蓝班(数学) 黄班(英语) 红班(体育)
蓝班(生物) NULL 黄班(地理) NULL NULL NULL 绿班(历史)
*/
(
id int identity,
title NVARCHAR(10),
[weekday] NVARCHAR(2)
)
INSERT #temp
SELECT N'语文', N'周一' UNION ALL
SELECT N'数学', N'周二' UNION ALL
SELECT N'生物', N'周一' UNION ALL
SELECT N'化学', N'周二' UNION ALL
SELECT N'地理', N'周三' UNION ALL
SELECT N'历史', N'周二'
GO
select * from #temp
--SQL
SELECT * from
(SELECT rowno=ROW_NUMBER() OVER(PARTITION BY [weekday] ORDER BY id), title,[weekday] FROM #TEMP) a
PIVOT
(MAX(title) FOR [weekday] IN([周一], [周二], [周三], [周四])) b
/*
1 语文 数学 地理 NULL
2 生物 化学 NULL NULL
3 NULL 历史 NULL NULL
*/
(select rowno=ROW_NUMBER() OVER(PARTITION BY DATENAME(dw,StartDate) order by ID),AffairKeys,DATENAME(dw,StartDate) from T_A_Affair) a
pivot
(max(AffairKeys) for DateName(dw,StartDate) IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) b一直提示:
消息 102,级别 15,状态 1,第 4 行
'(' 附近有语法错误。
我刚刚有试过您的代码是OK的,但是我的表里面WEEKDAY字段是用DATENAME(dw,StartDate)出来的,并不是存在的列select * from
(select rowno=ROW_NUMBER() OVER(PARTITION BY DATENAME(dw,StartDate) order by ID),AffairKeys,DATENAME(dw,StartDate) from T_A_Affair) a
pivot
(max(AffairKeys) for DateName(dw,StartDate) IN ([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])) b一直提示:
消息 102,级别 15,状态 1,第 4 行
'(' 附近有语法错误。
max(case [weekday] when '周一' then title else '' end) as '周一',
max(case [weekday] when '周二' then title else '' end) as '周二',
max(case [weekday] when '周三' then title else '' end) as '周三',
max(case [weekday] when '周四' then title else '' end) as '周四'
from
tb
use tempdb;
/*
create table A
(
id int identity,
title nvarchar(10) not null,
[weekday] nvarchar(10) not null
);
insert into A(title,[weekday]) values ('语文','周一');
insert into A(title,[weekday]) values ('数学','周二');
insert into A(title,[weekday]) values ('生物','周一');
insert into A(title,[weekday]) values ('化学','周二');
insert into A(title,[weekday]) values ('地理','周三');
insert into A(title,[weekday]) values ('历史','周二');
*/
select
MAX(case when B.[weekday] = '周一' then B.title end) as [周一],
MAX(case when B.[weekday] = '周二' then B.title end) as [周二],
MAX(case when B.[weekday] = '周三' then B.title end) as [周三],
MAX(case when B.[weekday] = '周四' then B.title end) as [周四]
from
(
select *,ROW_NUMBER() over(partition by A.[weekday] order by A.id) as [sort]
from A
) as B
group by B.sort;
use tempdb;
/*
create table A
(
id int identity,
title nvarchar(10) not null,
[date] date not null
);
insert into A(title,[date])
values
('语文','2011-05-02'),
('数学','2011-05-03'),
('生物','2011-05-02'),
('化学','2011-05-03'),
('地理','2011-05-04'),
('历史','2011-05-03');
*/select
MAX(case when DATENAME(dw,B.[date]) = '星期一' then B.title end) as [周一],
MAX(case when DATENAME(dw,B.[date]) = '星期二' then B.title end) as [周二],
MAX(case when DATENAME(dw,B.[date]) = '星期三' then B.title end) as [周三],
MAX(case when DATENAME(dw,B.[date]) = '星期四' then B.title end) as [周四]
from
(
select *,ROW_NUMBER() over(partition by DATENAME(dw,A.[date]) order by A.id) as [sort]
from A
) as B
group by B.sort;