测试中我有2个表,
t1:
id name address
1 Chris China
2 Peter Korea
3 Amanda Germanyt2:
id c_id item
1 1 XP
2 1 Office2003
3 1 WinRAR
4 1 GoogleToolbar
5 1 Foobar
6 2 XP
7 2 WinRAR
8 3 XP现希望在查询中得到结果
id name address XP Office2003 WinRAR GoogleToolbar Foobar
1 Chris China √ √ √ √
2 Peter Korea √ √
3 Amanda Germany √这个问题....是不是有些奇怪?
t1:
id name address
1 Chris China
2 Peter Korea
3 Amanda Germanyt2:
id c_id item
1 1 XP
2 1 Office2003
3 1 WinRAR
4 1 GoogleToolbar
5 1 Foobar
6 2 XP
7 2 WinRAR
8 3 XP现希望在查询中得到结果
id name address XP Office2003 WinRAR GoogleToolbar Foobar
1 Chris China √ √ √ √
2 Peter Korea √ √
3 Amanda Germany √这个问题....是不是有些奇怪?
解决方案 »
- 休完15天的假,问一个数据库查询优化的问题
- ~~~~~~~~~请教sql server 2005配置问题
- 各位大虾,关于SQL Server2000标准版的最大内存问题,多谢帮忙!
- 不敢再忽视Sql语句的能力。但是现在有一个自认为是比较难的问题。两个库之间表的同步。
- 拒绝了对对象 'syscolumns' (数据库 'mssqlsystemresource',架构 'sys')的 SELECT 权限
- 这个小选择题怎么做???快!!!
- sql全文索引不能正确搜索
- 为什么有的98系统下ODBC不能连接局域网另外一台机器Sqlserver2000的数据库?
- 不知道SQL Server Report Service的问题能不能算不算这个版块
- 关于SQL Server 2008 JDBC Driver的问题
- 更新视图的问题,求教
- 谁会用Excel透视表?一个难题!!
A.id,
A.name,
A.address,
Max(Case B.item When 'XP' Then '√' Else '' End) As XP,
Max(Case B.item When 'Office2003' Then '√' Else '' End) As Office2003,
Max(Case B.item When 'WinRAR' Then '√' Else '' End) As WinRAR,
Max(Case B.item When 'GoogleToolbar' Then '√' Else '' End) As GoogleToolbar,
Max(Case B.item When 'Foobar' Then '√' Else '' End) As Foobar
From t1 A
Inner Join t2 B
On A.id=B.c_id
Group By A.A.id,A.name,A.address
A.id,
A.name,
A.address,
Max(Case B.item When 'XP' Then '√' Else '' End) As XP,
Max(Case B.item When 'Office2003' Then '√' Else '' End) As Office2003,
Max(Case B.item When 'WinRAR' Then '√' Else '' End) As WinRAR,
Max(Case B.item When 'GoogleToolbar' Then '√' Else '' End) As GoogleToolbar,
Max(Case B.item When 'Foobar' Then '√' Else '' End) As Foobar
From t1 A
Inner Join t2 B
On A.id=B.c_id
Group By A.id,A.name,A.address
如果item不固定
Declare @S Varchar(8000)
Set @S=''
Select @S=@S+',Max(Case B.item When '''+item+''' Then ''√'' Else '''' End) As '+item From t2 Group By item
Select @S='Select A.id,A.name,A.address'+@S+' From t1 A Inner Join t2 B On A.id=B.c_id Group By A.id,A.name,A.address'
EXEC(@S)
insert t1 select 1,'Chris','China'
union all select 2,'Peter','Korea'
union all select 3,'Amanda','Germany'create table t2(id int,c_id int,item varchar(100))
insert t2 select 1,1,'XP'
union all select 2,1,'Office2003'
union all select 3,1,'WinRAR'
union all select 4,1,'GoogleToolbar'
union all select 5,1,'Foobar'
union all select 6,2,'XP'
union all select 7,2,'WinRAR'
union all select 8,3,'XP'
declare @s varchar(8000)
set @s='select c_id'
select @s=@s+',['+item+']=sum(case item when '''+item+''' then 1 else 0 end)' from t2 group by item
exec(@s+',t1.name,address from t2,t1 where t1.id=t2.c_id group by c_id,name,address')
xp=max(case item when 'xp' then '√' else '' end),
Office2003=max(case item when 'Office2003' then '√' else '' end),
WinRAR=max(case item when 'WinRAR' then '√' else '' end),
GoogleToolbar=max(case item when 'GoogleToolbar' then '√' else '' end),
Foobar=max(case item when 'Foobar' then '√' else '' end)
from t1
inner join t2 on t1.id=t2.c_id
group by t1.id,t1.name,address
Create Table t1
(id Int,
name Varchar(20),
address Varchar(20))
Create Table t2
(id Int,
c_id Int,
item Varchar(20))
--插入數據
Insert t1 Select 1, 'Chris', 'China'
Union All Select 2, 'Peter', 'Korea'
Union All Select 3, 'Amanda', 'Germany'Insert t2 Select 1, 1, 'XP'
Union All Select 2, 1, 'Office2003'
Union All Select 3, 1, 'WinRAR'
Union All Select 4, 1, 'GoogleToolbar'
Union All Select 5, 1, 'Foobar'
Union All Select 6, 2, 'XP'
Union All Select 7, 2, 'WinRAR'
Union All Select 8, 3, 'XP'
GO
--測試
--如果item固定
Select
A.id,
A.name,
A.address,
Max(Case B.item When 'XP' Then '√' Else '' End) As XP,
Max(Case B.item When 'Office2003' Then '√' Else '' End) As Office2003,
Max(Case B.item When 'WinRAR' Then '√' Else '' End) As WinRAR,
Max(Case B.item When 'GoogleToolbar' Then '√' Else '' End) As GoogleToolbar,
Max(Case B.item When 'Foobar' Then '√' Else '' End) As Foobar
From t1 A
Inner Join t2 B
On A.id=B.c_id
Group By A.id,A.name,A.address--如果item不固定
Declare @S Varchar(8000)
Set @S=''
Select @S=@S+',Max(Case B.item When '''+item+''' Then ''√'' Else '''' End) As '+item From t2 Group By item
Select @S='Select A.id,A.name,A.address'+@S+' From t1 A Inner Join t2 B On A.id=B.c_id Group By A.id,A.name,A.address'
EXEC(@S)
GO
--刪除測試環境
Drop Table t1,t2
--結果
/*
id name address Foobar GoogleToolbar Office2003 WinRAR XP
1 Chris China √ √ √ √ √
2 Peter Korea √ √
3 Amanda Germany √
*/
set @s='select c_id,t1.name,address'
select @s=@s+',['+item+']=max(case item when '''+item+''' then ''√'' else '''' end)' from t2 group by item
exec(@s+'from t2,t1 where t1.id=t2.c_id group by c_id,name,address')