表1:
ID FieldName
1 Name
2 NameCn
3 Sex
表2:
ID Uid FieldID FieldValue
1 1 1 ‘zhagnsan’
2 1 2 ‘张三’
3 1 2 ‘男’
4 2 1 ‘lisi’
5 2 2 ‘李四’
6 2 3 ‘男’想实现:
Name NameCn Sex
‘zhangsan’'张三' '男'
‘lisi’ '李四' '男'请问 我该怎么实现这个效果????
ID FieldName
1 Name
2 NameCn
3 Sex
表2:
ID Uid FieldID FieldValue
1 1 1 ‘zhagnsan’
2 1 2 ‘张三’
3 1 2 ‘男’
4 2 1 ‘lisi’
5 2 2 ‘李四’
6 2 3 ‘男’想实现:
Name NameCn Sex
‘zhangsan’'张三' '男'
‘lisi’ '李四' '男'请问 我该怎么实现这个效果????
select 1 as ID, 1 as Uid, 1 as FieldID,'zhagnsan' as FieldValue union all
select 2, 1, 2, '张三' union all
select 3, 1, 3, '男' union all
select 4, 2, 1, 'lisi' union all
select 5, 2, 2, '李四' union all
select 6, 2, 3, '男') T
go
select * into b from (
select 1 as ID, 'Name' as FieldName union all
select 2, 'NameCn' union all
select 3, 'Sex' ) T
goselect
max(case fieldname when 'Name' then FieldValue else '' end) [Name],
max(case fieldname when 'NameCn' then FieldValue else '' end) NameCn,
max(case fieldname when 'Sex' then FieldValue else '' end) Sex
from (
select uid,FieldName,FieldValue from a
inner join b on a.fieldId=b.id) T group by uiddrop table a
drop table b
这个是个静态的,如果有兴趣的话动态行列转