请问这个SQL怎么写，高手请进，跟帖有分

[copico]

数据表是这样的
GC MODEL STATUS
---- ------------------------------ ------
GSM SGH-T108+ Z
CDMA DVD-P248A H
CDMA STW-CG72 C
CDMA STW-CG72 H
CDMA N70 A
GSM SGH-T108+ Z
GSM SGH-T108+ Z
GSM SGH-T108+ G

我怎么查出这样的结果
GC MODEL A B C D E F G H Z
GSM SGH-T108+ 0 2
CDMA N70 A ..........

谢谢了

[liangCK]

顶楼上这么多 位..接分.

[dawugui]

--这是2000中用函数的方法,2005的方法见上.
create table tb(GC varchar(10), MODEL varchar(20), STATUS varchar(10))
insert into tb values('GSM' , 'SGH-T108+', 'Z') 
insert into tb values('CDMA', 'DVD-P248A', 'H') 
insert into tb values('CDMA', 'STW-CG72' , 'C') 
insert into tb values('CDMA', 'STW-CG72' , 'H') 
insert into tb values('CDMA', 'N70'      , 'A') 
insert into tb values('GSM' , 'SGH-T108+', 'Z') 
insert into tb values('GSM' , 'SGH-T108+', 'Z') 
insert into tb values('GSM' , 'SGH-T108+', 'G') 
go

--创建一个合并的函数
create function f_hb(@GC varchar(10) , @MODEL varchar(20))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ' ' + cast(STATUS as varchar) from tb where GC = @GC and MODEL = @MODEL
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go

--调用自定义函数得到结果：
select distinct GC , MODEL , dbo.f_hb(GC , MODEL) as STATUS from tb

drop table tb
drop function f_hb

/*
GC         MODEL                STATUS 
---------- -------------------- -------
CDMA       DVD-P248A            H
CDMA       N70                  A
CDMA       STW-CG72             C H
GSM        SGH-T108+            Z Z Z G

（所影响的行数为 4 行）
*/

[nzperfect]

很长久很长久的常见问题了.jf

[copico]

太多了太多了

[wzy_love_sly]

create table tb(gc varchar(10),MODEL varchar(50),status varchar(10))
insert into tb select 'GSM','SGH-T108+','z'
insert into tb select 'CDMA','DVD-P248A','h'
insert into tb select 'CDMA','STW-CG72','c'
insert into tb select 'CDMA','STW-CG72','h'
insert into tb select 'CDMA','N70','a'
insert into tb select 'GSM','SGH-T108+','z'
insert into tb select 'GSM','SGH-T108+','z'
insert into tb select 'GSM','SGH-T108+','g'

declare @sql varchar(8000)
select @sql = isnull(@sql+',','') + 'sum(case when status=''' + status + ''' then 1 else 0 end) [' + status + ']'
from (select 'a' as status union all 
select 'b' as status union all 
select 'c' as status union all 
select 'd' as status union all 
select 'e' as status union all 
select 'f' as status union all 
select 'g' as status union all 
select 'h' as status union all 
select 'i' as status union all 
select 'z' as status union all 
select 'k' as status
) as a
set @sql ='select gc,MODEL,'+ @sql + ' from tb group by gc,MODEL'
exec(@sql)

gc MODEL a b c d e f g h i z k
CDMA DVD-P248A 0 0 0 0 0 0 0 1 0 0 0
CDMA N70 1 0 0 0 0 0 0 0 0 0 0
GSM SGH-T108+ 0 0 0 0 0 0 1 0 0 3 0
CDMA STW-CG72 0 0 1 0 0 0 0 1 0 0 0

[wzy_love_sly]

create table tb(gc varchar(10),MODEL varchar(50),status varchar(10))
insert into tb select 'GSM','SGH-T108+','z'
insert into tb select 'CDMA','DVD-P248A','h'
insert into tb select 'CDMA','STW-CG72','c'
insert into tb select 'CDMA','STW-CG72','h'
insert into tb select 'CDMA','N70','a'
insert into tb select 'GSM','SGH-T108+','z'
insert into tb select 'GSM','SGH-T108+','z'
insert into tb select 'GSM','SGH-T108+','g'

select top 25 id=identity(int,0,1),status='a' into # from sysobjects a,sysobjects b
update # set status=char(ascii(status) +id)

declare @sql varchar(8000)
select @sql = isnull(@sql+',','') + 'sum(case when status=''' + status + ''' then 1 else 0 end) [' + status + ']'
from #
set @sql ='select gc,MODEL,'+ @sql + ' from tb group by gc,MODEL'
exec(@sql) 

gc	MODEL	a	b	c	d	e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	u	v	w	x	y
CDMA	DVD-P248A	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
CDMA	N70	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
GSM	SGH-T108+	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
CDMA	STW-CG72	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

[xiaoliaoyun]

create table #a (CC varchar(10),MODEL varchar(20),STATUS char(1))
go
insert into #a values( 'GSM','SGH-T108+','Z')
insert into #a values('CDMA','DVD-P248A','H')
insert into #a values('CDMA','STW-CG72', 'C')
insert into #a values('CDMA','STW-CG72', 'H')
insert into #a values('CDMA','N70',      'A')
insert into #a values( 'GSM','SGH-T108+','Z')
insert into #a values( 'GSM','SGH-T108+','Z')
insert into #a values( 'GSM','SGH-T108+','G')

select * from #a

declare @sql varchar(8000)
select @sql = 'select CC,MODEL'

select @sql = @sql + ',sum(case when STATUS = ''' +STATUS+ ''' then 1 else 0 end) as '''+STATUS+''''
 from (select distinct STATUS from #a) a

select @sql = @sql + ' from #a group by CC,MODEL'
exec (@sql)

drop table #a

[kelph]

手慢跟贴混点分

[copico]

结帖了，，太感谢你们了，都是高手啊

[moonsoup]

mark

[shenglei]

我是来接分的 o(∩_∩)o...哈哈

[huzhenqi2008]

支持你了``

[liuhua287]

????????????????

[appletan]

做下表连接嘛

[hunhun02]

up

[RongCha040]

在坛子里,二楼.三楼的代码见Ctrl + C 和 Ctrl + V 好多次了:P

[huayiluo]

.............

[zengdukan]

太高手了

[pmshuiyun]

不能re了

[jackeyabc]

不看，僅僅收藏而已