主子表转换显示的问题

Question

我有两个表
表一(子表)
      Type                           nvarchar(128)                 not   null, 
      Key                             nvarchar(64)                   not   null, 
      Value                         nvarchar(Max)                 not   null 有数据
CPA, qq, 2323232
CPA, msn, [email protected]
NBA,qq,4444444
NBA, msn, [email protected]
NBA, email, [email protected]表二(主表)Id                                       int                 not   null,
Type                           nvarchar(128)                 not   null, 
Name                           nvarchar(128)                 not   null有数据
1, CPA, CPA
2, NBA, NBA在子表里同一个Type可能有多行记录,每行的Type都对应主表的一个Type
现在的问题是,如何查询出所有的主表列, 并根据两个表里的Type相等 先把子表里同一个Type的行做列到行的转换变成一行,然后 与主表的字段一起显示出来.显示Id, Type, Name, qq, msn, email1,CPA, CPA,2323232,[email protected],NULL
2,NBA,NBA,4444444,[email protected],[email protected]
谢谢

Answer 1

select a.id , a.type , a.name,
  max(case b.Type when 'qq' then value else null end) qq,
  max(case b.Type when 'msn' then value else null end) msn,
  max(case b.Type when 'email' then value else null end) email
from 主表 a,子表 b
where a.type = b.type
group by a.id , a.type , a.name

Answer 2

--上面是个静态的SQL,下面的是包含动态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 enddrop table tb1

Answer 3

--应该用key
select a.id , a.type , a.name,
  max(case b.[key] when 'qq' then value else null end) qq,
  max(case b.[key] when 'msn' then value else null end) msn,
  max(case b.[key] when 'email' then value else null end) email
from 主表 a,子表 b
where a.type = b.type
group by a.id , a.type , a.name

Answer 4

create table 子表(Type varchar(10),[Key] varchar(10),Value varchar(20))
insert into 子表 values('CPA',   'qq'   ,   '2323232') 
insert into 子表 values('CPA',   'msn'  ,   '[email protected]')
insert into 子表 values('NBA',   'qq'   ,   '4444444') 
insert into 子表 values('NBA',   'msn'  ,   '[email protected]') 
insert into 子表 values('NBA',   'email',   '[email protected]') 
create table 主表(Id int,Type varchar(10),Name varchar(10))
insert into 主表 values(1, 'CPA', 'CPA') 
insert into 主表 values(2, 'NBA', 'NBA')
goselect a.id , a.type , a.name,
  max(case b.[key] when 'qq' then value else null end) qq,
  max(case b.[key] when 'msn' then value else null end) msn,
  max(case b.[key] when 'email' then value else null end) email
from 主表 a,子表 b
where a.type = b.type
group by a.id , a.type , a.namedrop table 子表,主表/*
id          type       name       qq                   msn                  email                
----------- ---------- ---------- -------------------- -------------------- -------------------- 
1           CPA        CPA        2323232              [email protected]          NULL
2           NBA        NBA        4444444              [email protected]     [email protected]（所影响的行数为 2 行）
*/

Answer 5

create table 子表(Type varchar(10),[Key] varchar(10),Value varchar(20))
insert into 子表 values('CPA',   'qq'   ,   '2323232') 
insert into 子表 values('CPA',   'msn'  ,   '[email protected]')
insert into 子表 values('NBA',   'qq'   ,   '4444444') 
insert into 子表 values('NBA',   'msn'  ,   '[email protected]') 
insert into 子表 values('NBA',   'email',   '[email protected]') 
create table 主表(Id int,Type varchar(10),Name varchar(10))
insert into 主表 values(1, 'CPA', 'CPA') 
insert into 主表 values(2, 'NBA', 'NBA')
go--静态SQL，指key只有qq,msn,email三种情况。
select a.id , a.type , a.name,
  max(case b.[key] when 'qq' then value else null end) qq,
  max(case b.[key] when 'msn' then value else null end) msn,
  max(case b.[key] when 'email' then value else null end) email
from 主表 a,子表 b
where a.type = b.type
group by a.id , a.type , a.name
/*
id          type       name       qq                   msn                  email                
----------- ---------- ---------- -------------------- -------------------- -------------------- 
1           CPA        CPA        2323232              [email protected]          NULL
2           NBA        NBA        4444444              [email protected]     [email protected]
（所影响的行数为 2 行）
*/--动态SQL，指key不止qq,msn,email三种情况。
declare @sql varchar(8000)
set @sql = 'select a.id , a.type , a.name'
select @sql = @sql + ' , max(case b.[key] when ''' + [key] + ''' then value else null end) [' + [key] + ']'
from (select distinct [key] from 主表 a,子表 b ) as a
set @sql = @sql + ' from 主表 a,子表 b group by a.id , a.type , a.name'
exec(@sql) 
/*
id          type       name       email                msn                  qq                   
----------- ---------- ---------- -------------------- -------------------- -------------------- 
1           CPA        CPA        [email protected]         [email protected]     4444444
2           NBA        NBA        [email protected]         [email protected]     4444444
*/drop table 子表,主表

Answer 6

--6楼错了点,这个对了.create table 子表(Type varchar(10),[Key] varchar(10),Value varchar(20))
insert into 子表 values('CPA',   'qq'   ,   '2323232') 
insert into 子表 values('CPA',   'msn'  ,   '[email protected]')
insert into 子表 values('NBA',   'qq'   ,   '4444444') 
insert into 子表 values('NBA',   'msn'  ,   '[email protected]') 
insert into 子表 values('NBA',   'email',   '[email protected]') 
create table 主表(Id int,Type varchar(10),Name varchar(10))
insert into 主表 values(1, 'CPA', 'CPA') 
insert into 主表 values(2, 'NBA', 'NBA')
go--静态SQL，指key只有qq,msn,email三种情况。
select a.id , a.type , a.name,
  max(case b.[key] when 'qq' then value else null end) qq,
  max(case b.[key] when 'msn' then value else null end) msn,
  max(case b.[key] when 'email' then value else null end) email
from 主表 a,子表 b
where a.type = b.type
group by a.id , a.type , a.name
/*
id          type       name       qq                   msn                  email                
----------- ---------- ---------- -------------------- -------------------- -------------------- 
1           CPA        CPA        2323232              [email protected]          NULL
2           NBA        NBA        4444444              [email protected]     [email protected]
（所影响的行数为 2 行）
*/--动态SQL，指key不止qq,msn,email三种情况。
declare @sql varchar(8000)
set @sql = 'select a.id , a.type , a.name'
select @sql = @sql + ' , max(case b.[key] when ''' + [key] + ''' then value else null end) [' + [key] + ']'
from (select distinct [key] from 主表 a,子表 b where a.type = b.type) as a
set @sql = @sql + ' from 主表 a,子表 b where a.type = b.type group by a.id , a.type , a.name'
exec(@sql) 
/*
id          type       name       email                msn                  qq                   
----------- ---------- ---------- -------------------- -------------------- -------------------- 
1           CPA        CPA        NULL                 [email protected]          2323232
2           NBA        NBA        [email protected]         [email protected]     4444444
*/drop table 子表,主表

Answer 7

DROP TABLE a,b
CREATE TABLE a(Type nvarchar(128) NOT NULL,[KEY] nvarchar(64) NOT NULL,Value NVARCHAR(1000) NOT NULL)   
INSERT a SELECT 'CPA','qq','2323232' 
UNION ALL SELECT 'CPA',  'msn','[email protected]' 
UNION ALL SELECT 'NBA','qq','4444444' 
UNION ALL SELECT 'NBA','msn','[email protected]' 
UNION ALL SELECT 'NBA','email','[email protected]' 
go
CREATE TABLE b(Id INT NOT NULL,Type NVARCHAR(128) NOT NULL ,Name NVARCHAR(128) NOT NULL)
INSERT b SELECT 1,'CPA','CPA' 
UNION ALL SELECT 2,'NBA','NBA' 
goDECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','')+'max(case when [Key]='''+[KEY]+''' then Value end) ['+[KEY]+']' FROM a GROUP BY [KEY] EXEC('select b.*, '+@sql+' from a inner join b on a.Type=b.Type Group by b.id,b.type,b.name')--result
/*Id          Type                           Name                           email                          msn                            qq                             
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
1           CPA                            CPA                            NULL                           [email protected]                    2323232
2           NBA                            NBA                            [email protected]                   [email protected]               4444444
*/