--第二個問題: create table #TB1 (CI nvarchar(10),[頻點] real) insert into #TB1 values('1',11) insert into #TB1 values('1',12) insert into #TB1 values('1',13) insert into #TB1 values('1',14) insert into #TB1 values('1',15) insert into #TB1 values('1',16) insert into #TB1 values('2',21) insert into #TB1 values('2',22) insert into #TB1 values('2',23) insert into #TB1 values('2',24) insert into #TB1 values('2',25) insert into #TB1 values('2',26) insert into #TB1 values('2',27) insert into #TB1 values('3',31) insert into #TB1 values('3',32) insert into #TB1 values('3',33) insert into #TB1 values('3',34) insert into #TB1 values('3',35) insert into #TB1 values('3',36) insert into #TB1 values('3',37) insert into #TB1 values('3',38)SELECT *,'頻點'+RTRIM(ROW_NUMBER() OVER(PARTITION BY CI ORDER BY [頻點])) re INTO #TB2 FROM #TB1declare @sql varchar(max) select @sql = isnull(@sql + '],[' , '') + re from (SELECT DISTINCT re FROM #TB2) a set @sql = 'select * from #tb2 a pivot (max([頻點]) for re in ([' + @sql + '])) b' EXEC(@sql)
http://bbs.csdn.net/topics/240002706
--第二個問題:
create table #TB1 (CI nvarchar(10),[頻點] real)
insert into #TB1 values('1',11)
insert into #TB1 values('1',12)
insert into #TB1 values('1',13)
insert into #TB1 values('1',14)
insert into #TB1 values('1',15)
insert into #TB1 values('1',16)
insert into #TB1 values('2',21)
insert into #TB1 values('2',22)
insert into #TB1 values('2',23)
insert into #TB1 values('2',24)
insert into #TB1 values('2',25)
insert into #TB1 values('2',26)
insert into #TB1 values('2',27)
insert into #TB1 values('3',31)
insert into #TB1 values('3',32)
insert into #TB1 values('3',33)
insert into #TB1 values('3',34)
insert into #TB1 values('3',35)
insert into #TB1 values('3',36)
insert into #TB1 values('3',37)
insert into #TB1 values('3',38)SELECT *,'頻點'+RTRIM(ROW_NUMBER() OVER(PARTITION BY CI ORDER BY [頻點])) re
INTO #TB2
FROM #TB1declare @sql varchar(max)
select @sql = isnull(@sql + '],[' , '') + re from (SELECT DISTINCT re FROM #TB2) a
set @sql = 'select * from #tb2 a pivot (max([頻點]) for re in ([' + @sql + '])) b'
EXEC(@sql)