关于SQLSERVER行转列的问题

fancycgx3650 2016-05-06 10:58:54
no name date signInStatus
01 张三 2015-04-01 OK
02 李四 2015-04-01 OK
03 王五 2015-04-01 迟到
01 张三 2015-04-02 OK
02 李四 2015-04-02 OK
03 王五 2015-04-02 OK
01 张三 2015-04-03 迟到
02 李四 2015-04-03 OK
03 王五 2015-04-03 缺席


请问怎么把表的内容转换为下表的格式

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
01 张三 OK OK 迟到
02 李四 OK OK OK
03 王五 迟到 OK 缺席
...全文
130 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
道素 2016-05-06
  • 打赏
  • 举报
回复

;WITH tb(no,NAME,[DATE],signInStatus) AS (
  SELECT  '01',N'张三','2015-04-01',N'OK' union all
  SELECT '02',N'李四','2015-04-01',N'OK' union all
  SELECT '03',N'王五','2015-04-01',N'迟到' union all
  SELECT '01',N'张三','2015-04-02',N'OK' union all
  SELECT '02',N'李四','2015-04-02',N'OK' union all
  SELECT '03',N'王五','2015-04-02',N'OK' union all
  SELECT '01',N'张三','2015-04-03',N'迟到' union all
  SELECT '02',N'李四','2015-04-03',N'OK' union all
  SELECT '03',N'王五','2015-04-03',N'缺席'
)

select * from (SELECT [name],[signInStatus],  DATEPART(dd,[DATE]) as d FROM tb) AS t
PIVOT(MAX(t.signInStatus) FOR d IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]))u   ---继续写下去
唐诗三百首 2016-05-06
  • 打赏
  • 举报
回复

create table fn
(no varchar(10),name varchar(10), date varchar(10), signInStatus varchar(10))

insert into fn
  select '01', '张三', '2015-04-01', 'OK' union all
  select '02', '李四', '2015-04-01', 'OK' union all
  select '03', '王五', '2015-04-01', '迟到' union all
  select '01', '张三', '2015-04-02', 'OK' union all
  select '02', '李四', '2015-04-02', 'OK' union all
  select '03', '王五', '2015-04-02', 'OK' union all
  select '01', '张三', '2015-04-03', '迟到' union all
  select '02', '李四', '2015-04-03', 'OK' union all
  select '03', '王五', '2015-04-03', '缺席'


declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
                    +' max(case when right(date,2)='''+right('00'+rtrim(number),2)+''' then signInStatus else '''' end)  '
                    +' '''+right('00'+rtrim(number),2)+''' '
  from master.dbo.spt_values
  where type='P' and number between 1 and 30

select @tsql='select no,name, '+@tsql
                     +'  from fn '
                     +'  group by no,name '
                     +'  order by no '

exec(@tsql)

/*
no         name       01         02         03         04         05         06         07         08         09         10         11         12         13         14         15         16         17         18         19         20         21         22         23         24         25         26         27         28         29         30
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
01         张三         OK         OK         迟到                                                                                                                                                                                                                                                                                                       
02         李四         OK         OK         OK                                                                                                                                                                                                                                                                                                       
03         王五         迟到         OK         缺席                                                                                                                                                                                                                                                                                                       

(3 row(s) affected)
*/
fancycgx3650 2016-05-06
  • 打赏
  • 举报
回复
请问用为什么要用MAX
引用 1 楼 ap0405140 的回复:

create table fn
(no varchar(10),name varchar(10), date varchar(10), signInStatus varchar(10))

insert into fn
  select '01', '张三', '2015-04-01', 'OK' union all
  select '02', '李四', '2015-04-01', 'OK' union all
  select '03', '王五', '2015-04-01', '迟到' union all
  select '01', '张三', '2015-04-02', 'OK' union all
  select '02', '李四', '2015-04-02', 'OK' union all
  select '03', '王五', '2015-04-02', 'OK' union all
  select '01', '张三', '2015-04-03', '迟到' union all
  select '02', '李四', '2015-04-03', 'OK' union all
  select '03', '王五', '2015-04-03', '缺席'


declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
                    +' max(case when right(date,2)='''+right('00'+rtrim(number),2)+''' then signInStatus else '''' end)  '
                    +' '''+right('00'+rtrim(number),2)+''' '
  from master.dbo.spt_values
  where type='P' and number between 1 and 30

select @tsql='select no,name, '+@tsql
                     +'  from fn '
                     +'  group by no,name '
                     +'  order by no '

exec(@tsql)

/*
no         name       01         02         03         04         05         06         07         08         09         10         11         12         13         14         15         16         17         18         19         20         21         22         23         24         25         26         27         28         29         30
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
01         张三         OK         OK         迟到                                                                                                                                                                                                                                                                                                       
02         李四         OK         OK         OK                                                                                                                                                                                                                                                                                                       
03         王五         迟到         OK         缺席                                                                                                                                                                                                                                                                                                       

(3 row(s) affected)
*/

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧