怎样把字符串 '数量' 转换成字段 数量
怎样把字符串 '数量' 转换成字段 数量
[解决办法]
行转列类似：create table wsp(test varchar(10),date1 datetime,price int)insert into wspselect 'a','2001-01-01',100union all select 'a','2001-01-02',101union all select 'a','2001-01-03',102union all select 'a','2001-01-04',103union all select 'b','2001-01-01',103union all select 'b','2001-01-02',104union all select 'b','2001-01-03',105union all select 'b','2001-01-05',106union all select 'c','2001-01-01',107union all select 'c','2001-01-02',108union all select 'c','2001-01-03',109union all select 'c','2001-01-04',110declare @sql nvarchar(4000)set @sql='select date1'select @sql=@sql+',['+test+']=sum(case test when '''+ test + ''' then price else 0 end)' from (select distinct test from wsp)aset @sql=@sql+' from wsp group by date1'exec sp_executesql @sql
[解决办法]
/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)
假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/
-------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/
-------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/
drop table tb
---------------------
---------------------
/*
如果上述两表互相换一下：即
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/
create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
--------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/
select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
drop table tb1
----------------------------05里的行列转换
--pivot,UNPIVOT,APPLY的应用
declare @t table(name varchar(10),question varchar(10),answer varchar(10))
insert into @t select '小李','问题1','答案1'
insert into @t select '小李','问题2','答案2'
insert into @t select '小李','问题3','答案3'
--回帖专用 ，哈哈
--注版权：charry0110
--PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,
--并在必要时对最终输出中所需的任何其余列值执行聚合.
--UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值.
1--一列转多列，多行转一行
select name,问题1,问题2,问题3 from @t
pivot
(max(answer) For question in --//可以使用相应的聚合函数
([问题1],[问题2],[问题3])
) as pvt
--result
--name 问题1 问题2 问题3
------------ ---------- ---------- ----------
--小李 答案1 答案2 答案3
2--多列转一列，一行转多行
SELECT name,question,answer
FROM
(SELECT name,问题1,问题2,问题3
FROM (
--一列转多列，多行转一行
select name,问题1,问题2,问题3 from @t
pivot
(max(answer) For question in
--count(answer),sum(answer)
([问题1],[问题2],[问题3])
) as pvt) t
--一列转多列，多行转一行
) p
UNPIVOT
(answer FOR question IN
(问题1,问题2,问题3)
)AS unpvt
--result
--小李 问题1 答案1
--小李 问题2 答案2
--小李 问题3 答案3
3--多行转一行
select replace ((SELECT question FROM @t N FOR XML AUTO),' <N question="',',')
--result
--,问题1"/>,问题2"/>,问题3"/>
select REPLACE(replace ((SELECT question FROM @t N FOR XML AUTO),' <N question="',','),'"/>', '')
--result
--,问题1,问题2,问题3
-- 查询处理
SELECT * FROM(SELECT DISTINCT name FROM @t)A
OUTER APPLY(SELECT
[values]= STUFF(REPLACE(REPLACE((SELECT question FROM @t N WHERE name = A.name FOR XML AUTO)
,' <N question="',','),'"/>', ''),1, 1, ''))N
--result
--小李 问题1,问题2,问题3
