现在有表1中的数据,希望将其转换成表2中的样子,如何实现。
【注】数据类型为字符串ID:int
FieldName:nvarchar(50)
FiledValue:nvarchar(50)表1(原始数据表)
ID FieldName FiledValue
1 Name1 holly1
1 Name2 holly2
1 Name3 holly3
2 Name1 roy1
2 Name2 roy2
2 Name3 roy3表2(期望结果表)
ID Name1 Name2 Name3
1 holly1 holly2 holly3
2 roy1 roy2 roy3
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(ID int, FieldName varchar(8), FiledValue varchar(8))
insert into #1
select 1, 'Name1', 'holly1' union all
select 1, 'Name2', 'holly2' union all
select 1, 'Name3', 'holly3' union all
select 2, 'Name1', 'roy1' union all
select 2, 'Name2', 'roy2' union all
select 2, 'Name3', 'roy3'select ID,
Name1=max(case FieldName when 'Name1' then FiledValue end),
Name2=max(case FieldName when 'Name2' then FiledValue end),
Name3=max(case FieldName when 'Name3' then FiledValue end)
from #1 group by ID/*
ID Name1 Name2 Name3
----------- -------- -------- --------
1 holly1 holly2 holly3
2 roy1 roy2 roy3
*/
,max(case when fidldName='Name2' then FiledValue) as Name2
,max(case when fidldName='Name3' then FiledValue) as Name3
from tb
group by id
select ID,
max(case FieldName when 'Name1' then FiledValue end) [Name1],
max(case FieldName when 'Name2' then FiledValue end) [Name2],
max(case FieldName when 'Name3' then FiledValue end) [Name3]
from tb1
group by ID
jack 2 2000
jack 3 3000
mike 1 4000
mike 2 5000
mike 3 6000现在想得到下面的结果 怎么转换name 一月 二月 三月
jack 1000 2000 3000
mike 4000 5000 6000这是一道面试题目,不用存储过程 ,只能用sql写
1、
declare @s varchar(8000)
set @s='select name'
select @s=@s+'['+month+']月,=sum(case when month '''+rtrim(month)+''' then month end)' from tablename group by month
exec(@s+' from tablename name')
2、
SELECT name,
SUM(CASE monTh WHEN 1 THEN money ELSE 0 END) AS '一月',
SUM(CASE monTh WHEN 2 THEN money ELSE 0 END) AS '二月',
SUM(CASE monTh WHEN 3 THEN money ELSE 0 END) AS '三月',
SUM(CASE monTh WHEN 4 THEN money ELSE 0 END) AS '四月'
FROM biaoming
GROUP BY name
set @sql = 'select id'
select @sql = @sql + ' , max(case fieldname when ''' + fieldname + ''' then fieldvalue end) [' + fieldname + ']'
from (select distinct fieldname from tb) as a
set @sql = @sql + ' from tb group by id'
print @sql
exec(@sql)
select id,
max(case when fidldName='Name1' then FiledValue) as Name1
,max(case when fidldName='Name2' then FiledValue) as Name2
,max(case when fidldName='Name3' then FiledValue) as Name3
from tb
group by id
PIVOT(MAX(FiledValue) FOR FieldName IN([Name1],[Name2],[Name3]))PVTID Name1 Name2 Name3
----------- -------- -------- --------
1 holly1 holly2 holly3
2 roy1 roy2 roy3(2 row(s) affected)
id int,
fieldname varchar(20),
filervalue varchar(20)
)
insert into #test select 1,'Name1','holly1' union all select
1,'Name2','holly2'union all select
1, 'Name3' ,'holly3' union all select
2 ,'Name1','roy1' union all select
2 ,'Name2' ,'roy2' union all select
2 ,'Name3' ,'roy3' declare @sql varchar(1000)set @sql = 'select a.id, ';select @sql = @sql + '(select max(case when b.fieldname = ''' + fieldname + ''' then b.filervalue end) from test b where b.id = a.id ) as ' + fieldname + ' , ' from #test group by fieldname
select @sql = @sql + '''测试'' from test a group by a.id'exec(@sql)
我也是来接分的