sql 行转列，并让列值作为行

forget_loves 2015-09-25 11:33:37
数据源

TaskName PositionName ApprovalRight PositionType ApprovalIndex
入伙联合检查 项目公司土建工程师 0 1 20
入伙联合检查 项目公司工程部副经理（土建） 0 1 40
入伙联合检查 项目公司工程部经理 0 1 60
入伙联合检查 项目公司分管工程副总经理 1 1 100
入伙联合检查 项目公司工程组组长 2 1 120
入伙联合检查 项目公司总经理 2 1 140
入伙联合检查 中心计划部项目经理 0 2 180
入伙联合检查 中心计划部分管副总经理 0 2 200
入伙联合检查 中心计划部总经理 2 2 220
入伙联合检查 分管计划总裁助理 2 2 240
入伙联合检查 物管公司总部分管业务副总经理 6 8 260
销售物料印刷制作 项目公司销售部策划主管 0 1 40
销售物料印刷制作 项目公司销售部经理 0 1 60
销售物料印刷制作 项目公司分管营销副总经理 1 1 100
销售物料印刷制作 项目公司总经理 2 1 140
销售物料印刷制作 中心营销部项目经理 0 2 160
销售物料印刷制作 中心营销部分管副总经理 0 2 180
销售物料印刷制作 中心营销部总经理 0 2 200
销售物料印刷制作 分管营销总裁助理 2 2 220
销售物料印刷制作 分管项目副总裁 2 2 240

需要变换为：
...全文
623 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tiger_Zhao 2015-09-29
  • 打赏
  • 举报
 回复 
/* 测试数据

WITH table1(TaskName,PositionName,ApprovalRight,PositionType,ApprovalIndex)AS(

    SELECT '入伙联合检查','项目公司土建工程师',0,1,20 UNION ALL

    SELECT '入伙联合检查','项目公司工程部副经理（土建）',0,1,40 UNION ALL

    SELECT '入伙联合检查','项目公司工程部经理',0,1,60 UNION ALL

    SELECT '入伙联合检查','项目公司分管工程副总经理',1,1,100 UNION ALL

    SELECT '入伙联合检查','项目公司工程组组长',2,1,120 UNION ALL

    SELECT '入伙联合检查','项目公司总经理',2,1,140 UNION ALL

    SELECT '入伙联合检查','中心计划部项目经理',0,2,180 UNION ALL

    SELECT '入伙联合检查','中心计划部分管副总经理',0,2,200 UNION ALL

    SELECT '入伙联合检查','中心计划部总经理',2,2,220 UNION ALL

    SELECT '入伙联合检查','分管计划总裁助理',2,2,240 UNION ALL

    SELECT '入伙联合检查','物管公司总部分管业务副总经理',6,8,260 UNION ALL

    SELECT '销售物料印刷制作','项目公司销售部策划主管',0,1,40 UNION ALL

    SELECT '销售物料印刷制作','项目公司销售部经理',0,1,60 UNION ALL

    SELECT '销售物料印刷制作','项目公司分管营销副总经理',1,1,100 UNION ALL

    SELECT '销售物料印刷制作','项目公司总经理',2,1,140 UNION ALL

    SELECT '销售物料印刷制作','中心营销部项目经理',0,2,160 UNION ALL

    SELECT '销售物料印刷制作','中心营销部分管副总经理',0,2,180 UNION ALL

    SELECT '销售物料印刷制作','中心营销部总经理',0,2,200 UNION ALL

    SELECT '销售物料印刷制作','分管营销总裁助理',2,2,220 UNION ALL

    SELECT '销售物料印刷制作','分管项目副总裁',2,2,240

)*/

SELECT Convert(nvarchar(15),TaskName) TaskName,

       Convert(nvarchar(15),PositionName) PositionName,

       Convert(nvarchar(15),PositionType) PositionType,

       PositionType * 100 + (ROW_NUMBER() OVER(PARTITION BY TaskName, PositionType

                                               ORDER BY ApprovalIndex)

                            ) rn

  INTO #t

  FROM table1



DECLARE @sql nvarchar(max)

DECLARE @columns nvarchar(max)



SET @columns = ''

    SELECT @columns=@columns+N',['+convert(nvarchar(11),rn)+N']'

      FROM #t

  GROUP BY rn

  ORDER BY rn



SET @columns = STUFF(@columns,1,1,'')



SET @sql = N'

SELECT *

  FROM (SELECT Convert(nvarchar(15),'''') TaskName,

               PositionType,

               rn

          FROM #t

       ) a1

 PIVOT (MAX(PositionType)

        FOR rn IN ('+@columns+N')

       ) p1

UNION ALL

SELECT *

  FROM (SELECT TaskName,

               PositionName,

               rn

          FROM #t

       ) a2

 PIVOT (MAX(PositionName)

        FOR rn IN ('+@columns+N')

       ) p2'



PRINT @sql



EXEC sp_executesql @sql

动态语句
SELECT *

  FROM (SELECT Convert(nvarchar(15),'') TaskName,

               PositionType,

               rn

          FROM #t

       ) a1

 PIVOT (MAX(PositionType)

        FOR rn IN ([101],[102],[103],[104],[105],[106],[201],[202],[203],[204],[205],[801])

       ) p1

UNION ALL

SELECT *

  FROM (SELECT TaskName,

               PositionName,

               rn

          FROM #t

       ) a2

 PIVOT (MAX(PositionName)

        FOR rn IN ([101],[102],[103],[104],[105],[106],[201],[202],[203],[204],[205],[801])

       ) p2

结果
TaskName           101                     102                         103                       104                       105                 106             201                 202                     203               204               205             801

------------------ ----------------------- --------------------------- ------------------------- ------------------------- ------------------- --------------- ------------------- ----------------------- ----------------- ----------------- --------------- -----------------------------

                   1                       1                           1                         1                         1                   1               2                   2                       2                 2                 2               8

入伙联合检查       项目公司土建工程师      项目公司工程部副经理（土建）项目公司工程部经理        项目公司分管工程副总经理  项目公司工程组组长  项目公司总经理  中心计划部项目经理  中心计划部分管副总经理  中心计划部总经理  分管计划总裁助理  NULL            物管公司总部分管业务副总经理

销售物料印刷制作   项目公司销售部策划主管  项目公司销售部经理          项目公司分管营销副总经理  项目公司总经理            NULL                NULL            中心营销部项目经理  中心营销部分管副总经理  中心营销部总经理  分管营销总裁助理  分管项目副总裁  NULL
-小蕾- 2015-09-29
  • 打赏
  • 举报
 回复
参考动态行转列： http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html
forget_loves 2015-09-26
  • 打赏
  • 举报
 回复
引用 3 楼 yangb0803 的回复:



;with tbl as
(
select '入伙联合检查' as TaskName, '项目公司土建工程师' as PositionName, 
'0'as ApprovalRight, '1'as PositionType, '20' as ApprovalIndex
union all
select '入伙联合检查', '项目公司工程部副经理（土建）', '0', '1', '40'
union all
select '入伙联合检查', '项目公司工程部经理', '0', '1', '60' 
union all
select '入伙联合检查', '项目公司分管工程副总经理', '1', '1', '100'
union all
select '入伙联合检查', '中心计划部项目经理', '0', '2', '180'
union all
select '入伙联合检查', '中心计划部分管副总经理', '0', '2', '200'
),
ct1 as 
(
select ROW_NUMBER() over(partition by TaskName, PositionType order by TaskName) as tid, * from tbl
),
ct2 as
(
select TaskName from tbl group by TaskName
)
select ct2.TaskName, 
t1.PositionName as [1], t2.PositionName as [1], 
t3.PositionName as [1], t4.PositionName as [1],
t5.PositionName as [2], t6.PositionName as [2]
from ct2 
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 1) as t1  -- 类别为1 的第一列数据
on ct2.TaskName = t1.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 2 and PositionType = 1) as t2  -- 类别为1 的第二列数据
on ct2.TaskName = t2.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 3 and PositionType = 1) as t3
on ct2.TaskName = t3.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 4 and PositionType = 1) as t4
on ct2.TaskName = t4.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 2) as t5  -- 类别为2 的第一列数据
on ct2.TaskName = t5.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 2) as t6  -- 类别为2 的第二列数据
on ct2.TaskName = t6.TaskName


-----------------------------------------------------------------------------
TaskName	 1	                     1	                                  1	                          1	                        2                                        	2
入伙联合检查	项目公司土建工程师	项目公司工程部副经理（土建）	项目公司工程部经理	项目公司分管工程副总经理	中心计划部项目经理	中心计划部项目经理
静态写法，需要你知道有多少类和多少列。
这样效率不高，而且列 是不确定的
qq_17482963 2015-09-25
  • 打赏
  • 举报
 回复
请问你是根据什么来划分列的呢
道玄希言 2015-09-25
  • 打赏
  • 举报
 回复 

;with tbl as
(
select '入伙联合检查' as TaskName, '项目公司土建工程师' as PositionName, 
'0'as ApprovalRight, '1'as PositionType, '20' as ApprovalIndex
union all
select '入伙联合检查', '项目公司工程部副经理（土建）', '0', '1', '40'
union all
select '入伙联合检查', '项目公司工程部经理', '0', '1', '60' 
union all
select '入伙联合检查', '项目公司分管工程副总经理', '1', '1', '100'
union all
select '入伙联合检查', '中心计划部项目经理', '0', '2', '180'
union all
select '入伙联合检查', '中心计划部分管副总经理', '0', '2', '200'
),
ct1 as 
(
select ROW_NUMBER() over(partition by TaskName, PositionType order by TaskName) as tid, * from tbl
),
ct2 as
(
select TaskName from tbl group by TaskName
)
select ct2.TaskName, 
t1.PositionName as [1], t2.PositionName as [1], 
t3.PositionName as [1], t4.PositionName as [1],
t5.PositionName as [2], t6.PositionName as [2]
from ct2 
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 1) as t1  -- 类别为1 的第一列数据
on ct2.TaskName = t1.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 2 and PositionType = 1) as t2  -- 类别为1 的第二列数据
on ct2.TaskName = t2.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 3 and PositionType = 1) as t3
on ct2.TaskName = t3.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 4 and PositionType = 1) as t4
on ct2.TaskName = t4.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 2) as t5  -- 类别为2 的第一列数据
on ct2.TaskName = t5.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 2 and PositionType = 2) as t6  -- 类别为2 的第二列数据
on ct2.TaskName = t6.TaskName

-----------------------------------------------------
TaskName	1	1	1	1	2	2
入伙联合检查	项目公司土建工程师	项目公司工程部副经理（土建）	项目公司工程部经理	项目公司分管工程副总经理	中心计划部项目经理	中心计划部分管副总经理
静态写法， 多少列，就多少个left join 连起来。
道玄希言 2015-09-25
  • 打赏
  • 举报
 回复 



;with tbl as
(
select '入伙联合检查' as TaskName, '项目公司土建工程师' as PositionName, 
'0'as ApprovalRight, '1'as PositionType, '20' as ApprovalIndex
union all
select '入伙联合检查', '项目公司工程部副经理（土建）', '0', '1', '40'
union all
select '入伙联合检查', '项目公司工程部经理', '0', '1', '60' 
union all
select '入伙联合检查', '项目公司分管工程副总经理', '1', '1', '100'
union all
select '入伙联合检查', '中心计划部项目经理', '0', '2', '180'
union all
select '入伙联合检查', '中心计划部分管副总经理', '0', '2', '200'
),
ct1 as 
(
select ROW_NUMBER() over(partition by TaskName, PositionType order by TaskName) as tid, * from tbl
),
ct2 as
(
select TaskName from tbl group by TaskName
)
select ct2.TaskName, 
t1.PositionName as [1], t2.PositionName as [1], 
t3.PositionName as [1], t4.PositionName as [1],
t5.PositionName as [2], t6.PositionName as [2]
from ct2 
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 1) as t1  -- 类别为1 的第一列数据
on ct2.TaskName = t1.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 2 and PositionType = 1) as t2  -- 类别为1 的第二列数据
on ct2.TaskName = t2.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 3 and PositionType = 1) as t3
on ct2.TaskName = t3.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 4 and PositionType = 1) as t4
on ct2.TaskName = t4.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 2) as t5  -- 类别为2 的第一列数据
on ct2.TaskName = t5.TaskName
left join
(select TaskName, PositionName from ct1 where tid = 1 and PositionType = 2) as t6  -- 类别为2 的第二列数据
on ct2.TaskName = t6.TaskName


-----------------------------------------------------------------------------
TaskName	 1	                     1	                                  1	                          1	                        2                                        	2
入伙联合检查	项目公司土建工程师	项目公司工程部副经理（土建）	项目公司工程部经理	项目公司分管工程副总经理	中心计划部项目经理	中心计划部项目经理
静态写法，需要你知道有多少类和多少列。
forget_loves 2015-09-25
  • 打赏
  • 举报
 回复
PositionType
SQL语句集锦
例如，将不同季度的销售数据从多列转为一列显示，每个季度作为单独的行值。 2. **多行补充.sql**：这可能涉及合并多行数据，比如使用UNION或UNION ALL来组合不同的查询结果，或者在处理缺失数据时用COALESCE函数...
sql如何行转列，列转行
我们都知道在sql查询中，union 是增行，join是增列，但是如果碰到需要行转列，列转行时，一味的使用 join来完成则会变的异常繁杂。如把一天24小时的行转换成列，则需要join24次，既不方便管理也使sql运行效率很慢，...
SQL 行转列，列转行
SQL 学习记录
SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）
其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列...
SQL中怎么将行转成列？
首先，假设我们有一张分数表（tb_score），表中的数据如下图：然后，我们再来看一下转换之后需要得到的结果，如下图：可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据...
疑难问题

22,300

社区成员

121,734

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章