请问一个行转列的问题

[无锡阔微信息科技]

数据
FinterID FitemID fdate fheight fheight2
5088 29894 200906 30 30
5088 29894 200907 30 30

查询生成结果
FinterID FitemID fdate 200906fheight 200906fheight2 200907fheight 200907fheight2
5088 29894 200906 30 30 30 30

[guguda2008]

动态生成？有几行加2*几列？

[无锡阔微信息科技]

我看了一下，但还是不懂，请教了

[无锡阔微信息科技]

请牛人们给个答案啊

[kiddy_cry]

坐等

[guguda2008]

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
FinterID VARCHAR(50)  
,FitemID  VARCHAR(50)
,fdate    VARCHAR(50)
,fheight  INT
,fheight2 INT
)
INSERT INTO TB
SELECT '5088','29894','200906',   30,   30 UNION ALL
SELECT '5088','29894','200907',   30,      30 
/*
SELECT FINTERID,FitemID
,MAX(CASE WHEN FDATE='200906' THEN FHEIGHT ELSE NULL END)  '200906HEIGHT'
,MAX(CASE WHEN FDATE='200906' THEN FHEIGHT2 ELSE NULL END)  '200906HEIGHT2'
,MAX(CASE WHEN FDATE='200907' THEN FHEIGHT ELSE NULL END)  '200907HEIGHT'
,MAX(CASE WHEN FDATE='200907' THEN FHEIGHT2 ELSE NULL END)  '200907HEIGHT2'
FROM TB
GROUP BY FINTERID,FitemID
*/
DECLARE @STR VARCHAR(8000)
SELECT @STR=''
SELECT @STR=@STR+'
,MAX(CASE WHEN FDATE='''+FDATE+''' THEN FHEIGHT ELSE NULL END)  '''+FDATE+'HEIGHT''
,MAX(CASE WHEN FDATE='''+FDATE+''' THEN FHEIGHT2 ELSE NULL END)  '''+FDATE+'HEIGHT2'''
FROM (
SELECT DISTINCT FDATE FROM TB) T

SELECT @STR='SELECT FINTERID,FitemID
'+@STR+'
FROM TB
GROUP BY FINTERID,FitemID'

EXEC (@STR)

/*
(2 行受影响)
FINTERID                                           FitemID                                            200906HEIGHT 200906HEIGHT2 200907HEIGHT 200907HEIGHT2
-------------------------------------------------- -------------------------------------------------- ------------ ------------- ------------ -------------
5088                                               29894                                              30           30            30           30
警告: 聚合或其他 SET 操作消除了空值。

(1 行受影响)
*/

[guguda2008]

如果不是按前两个字段分组的话自己改改GROUP BY和SELECT 前边两个字段

[--小F--]

select 
  a.FinterID,a.FitemID,a.fdate,
  a.fheight as 200906fheight,
  a.fheight2 as 200906fheight2,
  b.fheight as 200907fheight,
  b.fheight2 as 200907fheight2
from
  (select * from tb where fdate='200906')a
left join 
  (select * from tb where fdate='200907')b 
on
  a.FinterID=b.FinterID and a.FitemID=b.FitemID

[nianran520]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([FinterID] varchar(10),[FitemID] varchar(10),[fdate] varchar(10),[fheight] int,[fheight2] int)
insert [tb]
select 5088,29894,200906,30,30 union all
select 5088,29894,200907,30,30

--select * from [tb]

declare @sql varchar(8000)
select @sql = 'select [FinterID],[FitemID],min([fdate]) as [fdate]'

select @sql = @sql + ',max(case fdate when '''+fdate+''' then fheight else 0 end) as ['+fdate+'fheight],
max(case fdate when '''+fdate+''' then fheight2 else 0 end) as ['+fdate+'fheight2]'
from (select distinct fdate from tb) t

select @sql = @sql + ' from tb group by [FinterID],[FitemID]'

--print @sql

exec(@sql)
------------------------------
5088	29894	200906	30	30	30	30

[快乐_石头]

相同
會不會出現200908?

[快乐_石头]

[Quote=引用 9 楼 happy_stone 的回复:]
相同
會不會出現200908?

[/Quote]
相同 nterID FitemID
會不會出現200908?

[dawugui]

create table tb(FinterID int, FitemID int, fdate varchar(10), fheight int, fheight2 int)
insert into tb values(5088 , 29894 , 200906 , 30 , 30 )
insert into tb values(5088 , 29894 , 200907 , 30 , 30 )
go


declare @sql varchar(8000)
set @sql = 'select FinterID , FitemID '
select @sql = @sql + ' , max(case fdate when ''' + fdate + ''' then fheight else 0 end) [' + fdate + 'fheight1]'
                   + ' , max(case fdate when ''' + fdate + ''' then fheight2 else 0 end) [' + fdate + 'fheight2]'
from (select distinct fdate from tb) as a
set @sql = @sql + ' from tb group by FinterID , FitemID '
exec(@sql)

drop table tb

/*
FinterID    FitemID     200906fheight1 200906fheight2 200907fheight1 200907fheight2 
----------- ----------- -------------- -------------- -------------- -------------- 
5088        29894       30             30             30             30
*/