目前有一个表
userid configname(text) configvalue(text)
---------------------------------
1 a 4
1 b ff
1 c 100
1 d rr
2 a 6
2 b iiiiii
2 c 1000
2 d hhh
3 a 9
3 b fdsa
3 c 5555
3 d dfdad如何将上面表转换为下面格式的表
id a b c d
--------------------------
1 4 ff 100 rr
2 6 iiiiii 1000 hhh
3 9 fdsa 5555 dfdad
userid configname(text) configvalue(text)
---------------------------------
1 a 4
1 b ff
1 c 100
1 d rr
2 a 6
2 b iiiiii
2 c 1000
2 d hhh
3 a 9
3 b fdsa
3 c 5555
3 d dfdad如何将上面表转换为下面格式的表
id a b c d
--------------------------
1 4 ff 100 rr
2 6 iiiiii 1000 hhh
3 9 fdsa 5555 dfdad
解决方案 »
- 针对程序集 'SqlServerProject' 的 CREATE ASSEMBLY 失败,因为程序集 'SqlServerProject' 未获授权,不满足 PERMISSION_SET = EXT
- 存储过程参数问题
- 存储过程 declare 一个变量赋值的问题
- 请教一个简单的问题?
- 哥哥姐姐们! 怎样获取某个软件提交到SQL Server 的SQL 语句呢?
- 关于DTS包的问题
- 怎么样一次性将多行数据插入表?
- 再求一个存储过程!
- 一个表字段的值作为另一个表名称查询
- 几十万条记录的数据库,每天备份,删除,表空间和回滚段的问题?
- MS-SQL里用于存放数据集合的东东是什么?
- 请教!如何去除数据表字段的 unique 约束?
create table t(userid int,configname varchar(10),configvalue varchar(10))
insert into t select 1,'a','4'
insert into t select 1,'b','ff'
insert into t select 1,'c','100'
insert into t select 1,'d','rr'
insert into t select 2,'a','6'
insert into t select 2,'b','iiiiii'
insert into t select 2,'c','1000'
insert into t select 2,'d','hhh'
insert into t select 3,'a','9'
insert into t select 3,'b','fdsa'
insert into t select 3,'c','5555'
insert into t select 3,'d','dfdad'declare @sql varchar(8000)
set @sql='select userid as id'
select @sql=@sql+',['+configname+']=max(case configname when '''+configname+''' then configvalue end)'
from t group by configname order by configname
set @sql=@sql+' from t group by userid'
exec(@sql)
/*
id a b c d
----------- ---------- ---------- ---------- ----------
1 4 ff 100 rr
2 6 iiiiii 1000 hhh
3 9 fdsa 5555 dfdad
*/
drop table t
CASE configname WHEN 'b' THEN configvalue END ,
CASE configname WHEN 'c' THEN configvalue END ,
CASE configname WHEN 'd' THEN configvalue END from 表
a=max(case configname when 'a' then configvalue end),
b=max(case configname when 'b' then configvalue end),
c=max(case configname when 'c' then configvalue end),
d=max(case configname when 'd' then configvalue end)
from 表
group by userid
insert into t select 1,'a','4'
insert into t select 1,'b','ff'
insert into t select 1,'c','100'
insert into t select 1,'d','rr'
insert into t select 2,'a','6'
insert into t select 2,'b','iiiiii'
insert into t select 2,'c','1000'
insert into t select 2,'d','hhh'
insert into t select 3,'a','9'
insert into t select 3,'b','fdsa'
insert into t select 3,'c','5555'
insert into t select 3,'d','dfdad'
select * from t
pivot
(
max(configvalue)
for configname in ([a],[b],[c],[d])
)as pivt--结果
userid a b c d
----------- ---------- ---------- ---------- ----------
1 4 ff 100 rr
2 6 iiiiii 1000 hhh
3 9 fdsa 5555 dfdad(3 行受影响)--删除环境
drop table t