SQL server 动态行转列

用聚合函数配合CASE语句实现行转列功能:

现在分享一下具体实现代码:

转换前效果:

PlanNamePlanTypePlanLimit
计划1计划类型1RMB 1,000,000
计划1计划类型2RMB 1,000,000
计划1计划类型3RMB 1,000,000
计划2计划类型1RMB 1,000,000
计划2计划类型2RMB 1,000,000
计划2计划类型3RMB 1,000,000
计划3计划类型1RMB 1,000,000
计划3计划类型2RMB 1,000,000
计划3计划类型3RMB 1,000,000

 

 

 

 

 

 

 

 

 

1、静态实现行转列

 1 with main as
 2 (
 3     select '计划1' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit
 4     union all
 5     select '计划1' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit
 6     union all
 7     select '计划1' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit
 8     union all
 9     select '计划2' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit
10     union all
11     select '计划2' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit
12     union all
13     select '计划2' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit
14     union all
15     select '计划3' as PlanName,'计划类型1' as PlanType,'RMB 1,000,000' as PlanLimit
16     union all
17     select '计划3' as PlanName,'计划类型2' as PlanType,'RMB 1,000,000' as PlanLimit
18     union all
19     select '计划3' as PlanName,'计划类型3' as PlanType,'RMB 1,000,000' as PlanLimit
20 )
21 select PlanType as [计划]
22     ,'计划1'=max(case PlanName when '计划1' then PlanLimit else null end)
23     ,'计划2'=max(case PlanName when '计划2' then PlanLimit else null end)
24     ,'计划3'=max(case PlanName when '计划3' then PlanLimit else null end)
25 from main 
26 where 1=1
27 group by PlanType

2、动态实现行转列

 1 -- =============================================
 2 -- Author:        <Anne>
 3 -- Create date: <2016/04/27>
 4 -- Description:    <查询SP_AnneTest表的数据>
 5 -- =============================================
 6 CREATE PROCEDURE [dbo].[SP_AnneTest]
 7 (
 8 )
 9 AS
10     declare @sql varchar(8000)
11 BEGIN
12     set @sql=''
13 
14     select @sql=@sql+','+''''+[PlanName]+''''+'=max(case PlanName when '''+[PlanName]+''' then PlanLimit else null end)'
15     from main
16     where 1=1
17     group by PlanType
18     set @sql='select PlanType as [''计划'']'+@sql+' 
19             from main 
20             where 1=1
21             group by PlanType'
22     
23     print @sql
24     exec(@sql)
25 
26 END
27 GO

 

实现效果:

计划计划1计划2计划3
计划类型1RMB 1,000,000RMB 1,000,000RMB 1,000,000
计划类型2RMB 1,000,000RMB 1,000,000RMB 1,000,000
计划类型3RMB 1,000,000RMB 1,000,000RMB 1,000,000

 

转载于:https://www.cnblogs.com/AnneHan/p/5541961.html

### 回答1: SQL Server动态行转列是一种将行数据以列的形式展示的技术。在传统的表结构中,数据是以行的形式存储的,每一行代表一个记录。而动态行转列则是将某一列的值作为新的列头,然后将其对应的值作为新列的值。 SQL Server中可以使用PIVOT函数来实现动态行转列。该函数可以将一个包含重复值的列转换为多个独立的列,并将重复值作为行数据填充到对应的新列中。 使用PIVOT函数的语法如下所示: SELECT 列列表 FROM 数据源 PIVOT (聚合函数(待转换的列) FOR 列头 IN (列值1, 列值2, ...)) AS 别名; 其中,列列表是需要查询的列;数据源是要从中查询的表或视图;聚合函数是在转换过程中用于聚合数据的函数,可以是SUM、COUNT、AVG等;列头是转换后的新列头的名称;列值是待转换的列中可能出现的值。 例如,假设有一个表格包含有学生的姓名、科目以及对应的成绩,我们希望将科目转换为动态的列头,以学生姓名作为行数据填充到对应的新列中。可以使用以下SQL语句实现: SELECT * FROM (SELECT 姓名, 科目, 成绩 FROM 学生成绩表) AS 原表 PIVOT (AVG(成绩) FOR 科目 IN (数学, 语文, 英语)) AS 转换后的表; 这样就可以将原表中的科目列动态地转换为了数学、语文、英语三个新的列,并将每个学生的成绩填充到对应的新列中。 总之,SQL Server动态行转列可以让我们更加方便地展示和分析数据,提高了数据处理的灵活性和效率。 ### 回答2: 在SQL Server中,可以使用动态行转列的方法将行数据转换为列数据。动态行转列是一种常用的数据转换技术,特别适用于需要将具有多个行值的列转换为多个列的情况。 示例中,假设有一个名为Employees的表,其中包含员工的姓名、部门和薪水。初始表结构如下: 姓名 | 部门 | 薪水 张三 | 销售 | 5000 李四 | 财务 | 6000 王五 | 开发 | 7000 现在我们希望将每个部门的薪水作为新的列,得到以下结果: 姓名 | 销售 | 财务 | 开发 张三 | 5000 | NULL | NULL 李四 | NULL | 6000 | NULL 王五 | NULL | NULL | 7000 要实现此目标,可以使用动态行转列的方法,步骤如下: 1. 使用动态SQL语句创建一个包含部门名称的临时表,例如: ```sql DECLARE @department TABLE (name VARCHAR(50)) INSERT INTO @department SELECT DISTINCT department FROM Employees ``` 2. 使用动态SQL语句构建一个包含员工姓名和对应部门薪水的动态查询语句,例如: ```sql DECLARE @sql NVARCHAR(MAX) SET @sql = 'SELECT e.name, ' + STUFF((SELECT DISTINCT ', MAX(CASE WHEN e.department = ''' + d.name + ''' THEN e.salary ELSE NULL END) AS ' + d.name FROM @department d FOR XML PATH('')), 1, 2, '') + ' FROM Employees e GROUP BY e.name' 3. 执行动态查询语句,得到转换后的结果,例如: ```sql EXECUTE sp_executesql @sql ``` 通过以上步骤,我们可以将原始的行数据转换为列数据,并得到对应部门的薪水信息。 值得注意的是,动态行转列需要谨慎使用,特别是当存在大量数据或者动态列数量很多时,可能会导致性能下降或者查询效果不佳。因此,在实际使用中需要根据具体情况进行评估和优化。 ### 回答3: SQL Server中可以使用Pivot操作来实现动态行转列。Pivot操作可以将一列的值转换为多个列,并根据某一列的值进行分类。 具体步骤如下: 1. 首先,根据需要的列名和查询的结果,使用动态SQL生成Pivot查询语句。 2. 使用EXECUTE语句执行动态SQL,并将结果存储在一个临时表中。 3. 使用SELECT语句从临时表中查询转置后的结果。 以下是一个示例: ```sql DECLARE @cols AS NVARCHAR(MAX) DECLARE @query AS NVARCHAR(MAX) -- 创建动态列名 SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(category) FROM yourTable FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') SET @query = 'SELECT * FROM (SELECT id, category, value FROM yourTable) t PIVOT(MAX(value) FOR category IN (' + @cols + ')) p' -- 创建临时表并执行动态SQL SELECT * INTO #tmpTable FROM yourTable EXECUTE(@query) -- 查询转置后的结果 SELECT * FROM #tmpTable -- 清除临时表 DROP TABLE #tmpTable ``` 以上示例中,yourTable是需要转置的原始表,category列是需要转置的列名,id列是用于分类的列,value列是需要转置的值。在示例中,使用了Pivot函数将category列的值转换为多个列名,并将相应的value值填充到对应的列中。 需要注意的是,动态SQL的使用需要谨慎,需要考虑到SQL注入的风险,并且确保生成的动态SQL语句是正确和安全的。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值