sql语句：列转行问题，pivot,求助。紧急求助。

[maolvqd]

create table #test
(
country varchar(20),
[name] varchar(20),
addr varchar(100)
)

insert into #test
select 'CN','wang','wabuf'

insert into #test
select 'US','zhang','mnt'

insert into #test
select 'EU','li','wqwe'

insert into #test
select 'JP','sun','wett'


select * from #test



目前表是这样：

country name addr
----------- -------------------- --------
CN wang wabuf
US zhang mnt
EU li wqwe
JP sun wett


如何使用pivot将这个表变成下面这样：


项目 CN US EU JP
----------- -------------------- -------- --------
name wang zhang li sun
addr wabuf mnt wqwe wett

[maolvqd]

在线等啊。各位兄弟姐妹，帮帮忙啊。

[lzfrab]

帮UP...

[maolvqd]

没人会吗？

[you_tube]

SELECT addr,cn ,us,eu,jp
FROM (SELECT country ,NAME,addr FROM #test) p
PIVOT
( MAX  
 
([name]) FOR country IN (cn ,us,eu,jp)
) AS pvt


addr                                                                                                 cn                   us                   eu                   jp
------------------------- -------------------- -------------------- -------------------- --------------------
mnt                                                                                                  NULL                 zhang                NULL                 NULL
wabuf                                                                                                wang                 NULL                 NULL                 NULL
wett                                                                                                 NULL                 NULL                 NULL                 sun
wqwe                                                                                                 NULL                 NULL                 li                   NULL

[dawugui]

create table test ( country varchar(20), [name] varchar(20), addr varchar(20) ) 
insert into test select 'CN','wang','wabuf' 
insert into test select 'US','zhang','mnt' 
insert into test select 'EU','li','wqwe' 
insert into test select 'JP','sun','wett' 

SELECT * FROM 
(
  SELECT 项目,country,项目情况 FROM 
     (SELECT country,name,addr FROM Test) p
  UNPIVOT
     (项目情况 FOR 项目 IN (name,addr))AS unpvt
) T
PIVOT
(MAX(项目情况)  FOR country in ([CN],[US],[EU],[JP]))AS pt

drop table test

/*
项目                                                                                                                               CN                   US                   EU                   JP
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------
addr                                                                                                                             wabuf                mnt                  wqwe                 wett
name                                                                                                                             wang                 zhang                li                   sun

(2 行受影响)
*/

[dawugui]

--重新整理一下结果.

create table test ( country varchar(20), [name] varchar(20), addr varchar(20) ) 
insert into test select 'CN','wang','wabuf' 
insert into test select 'US','zhang','mnt' 
insert into test select 'EU','li','wqwe' 
insert into test select 'JP','sun','wett' 

SELECT * FROM 
(
  SELECT 项目,country,项目情况 FROM 
     (SELECT country,name,addr FROM Test) p
  UNPIVOT
     (项目情况 FOR 项目 IN (name,addr))AS unpvt
) T
PIVOT
(MAX(项目情况)  FOR country in ([CN],[US],[EU],[JP]))AS pt

drop table test

/*
项目    CN    US    EU   JP
------------- ----- ---- -----
addr    wabuf mnt   wqwe wett
name    wang  zhang li   sun

(2 行受影响)
*/

[-狙击手-]

SELECT *
FROM  
   (SELECT 项目='name',country,[name] as col from test
    union all
    select 'addr',country,[addr] from test ) p 
PIVOT 
   ( max (col)  
    FOR country IN ([cn],[us],[eu],[jp] ) 
   )AS unpvt 

[maolvqd]

4楼，5楼、6楼、7楼都不对。

我亲自测试了。

7楼的兄弟，我这个

目前表是这样：

country name addr
----------- -------------------- --------
CN wang wabuf
US zhang mnt
EU li wqwe
JP sun wett

这个表有多少行是不固定的。

[Ymeng_0226]

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。

所以你要首先看你符合这运用pivod的条件不符合，若不然，运用是没有意义的。

[ks_reny]

又是行列互換呀.欄位要是不固定的話,要用到遊標.
我之前做了一個和你的比較類似.

  Alter Procedure RRRRR -------- 'B00' 
@PP char(6)='H00' 
As 

Begin 
   Create Table #RRR(Provide char(50)) 

   Select DefectCode,SubString(DefectCode,1,1)+Rtrim(right(Rtrim(DefectCode),2)) as Num,Sum(DefectQty) as DefectQty 
       into #PPP 
  From Qc_DefectDtl                ------------某一製程的缺點項目及缺點數 
  Where Dotime='2007-01-18' And Defectcond ='RE' And ProvideCode is not null and ProvideCode=@PP 
  Group By ProvideCode,Dotime,DefectCode 

Select 
      IPQCDefectCode, 
       Case When Len(IPQCDefectName)>16 Then SubString(IPQCDefectName,1,16) 
               Else  IPQCDefectName End as  IPQCDefectName 
     Into #IPQCDefectName                              -------------------把缺點名稱長度大於16的截為16位 
From IPQCDefectClass 

Declare @ProvideCode char(10) 
Declare @DefectCode  char(10) 
Declare @Num Char(10) 
Declare @SQL varchar(500) 
Declare @SQLL varchar(500) 
Declare rps_cursor Cursor For                             ------某一製程的缺點及對應的附加字段 
Select DefectCode,SubString(DefectCode,1,1)+Rtrim(right(Rtrim(DefectCode),2)) as Num  From Qc_DefectDtl 
Where Dotime='2007-01-18' And Defectcond ='RE' And ProvideCode is not null and ProvideCode=@PP 
Group By ProvideCode,Dotime,DefectCode 

Open rps_cursor 
Fetch Next  From rps_cursor into @DefectCode,@Num 
WHILE @@FETCH_STATUS = 0                                     --------------------動態增加字段 
Begin 
   Select  @SQL=' Alter Table #RRR Add ['+@DefectCode+'] varchar(100) Null' 
     Select @SQL=@SQL+'   Alter Table #RRR  Add ['+@num+'] varchar(100) ' 
  Exec(@SQL) 
Fetch Next From   rps_cursor into @DefectCode,@Num 
End 
Close rps_cursor 
---Deallocate rps_cursor 

Insert into #RRR(Provide) 
Values(@PP) 
   
Open rps_cursor 

Fetch Next  From rps_cursor into @DefectCode,@Num 
WHILE @@FETCH_STATUS = 0                     
Begin                                                                                                    --------------------動態更新表字段內容 
    Select @SQLL='Update t1 Set ['+@Num+']=t2.DefectQty From #RRR t1,#PPP t2 Where  '''+@DefectCode+'''=t2.DefectCode' 
   Exec(@SQLL) 
    Select  @SQLL='   update t1 set ['+@DefectCode+']= t2.IPQCDefectName   From #RRR t1, #IPQCDefectName t2 where '''+@DefectCode+'''=t2.IPQCDefectCode ' 
    Exec(@SQLL) 

---- Select @DefectCode,@Num 
Fetch Next From   rps_cursor into @DefectCode,@Num 
End 
Close rps_cursor 
Deallocate rps_cursor 


Select * From #RRR 


End