1.获取动态列
select @Colstring1 = isnull(@Colstring1 + ',' , '' ) + isnull( '['+CommodityName+']' , '' ) from 表1
2.使用pivot Table
下面是in部分的使用,你看一下pivot table怎么用就行了
IN('+@Colstring1+'))
select @Colstring1 = isnull(@Colstring1 + ',' , '' ) + isnull( '['+CommodityName+']' , '' ) from 表1
2.使用pivot Table
下面是in部分的使用,你看一下pivot table怎么用就行了
IN('+@Colstring1+'))
if object_id('Table_A') is not null drop table Table_A
gocreate table Table_A (ID int ,Name varchar(10))
insert into Table_A Values(1, 'A1')
insert into Table_A Values(2, 'A2')
insert into Table_A Values(3, 'A3')
insert into Table_A Values(4, 'A4')
insert into Table_A Values(5, 'A5')
insert into Table_A Values(6, 'A6')
insert into Table_A Values(7, 'A7')
insert into Table_A Values(8, 'A8')
insert into Table_A Values(9, 'A9')
insert into Table_A Values(10, 'A10')
insert into Table_A Values(11, 'A11')
insert into Table_A Values(12, 'A12')
insert into Table_A Values(13, 'A13')
insert into Table_A Values(14, 'A14')
insert into Table_A Values(15, 'A15')
insert into Table_A Values(16, 'A16')
insert into Table_A Values(17, 'A17')
insert into Table_A Values(18, 'A18')
insert into Table_A Values(19, 'A19')
insert into Table_A Values(20, 'A20')
insert into Table_A Values(21, 'A21')
insert into Table_A Values(22, 'A22')
insert into Table_A Values(23, 'A23')
insert into Table_A Values(24, 'A24')
insert into Table_A Values(25, 'A25')
insert into Table_A Values(26, 'A26')
insert into Table_A Values(27, 'A27')
insert into Table_A Values(28, 'A28')
insert into Table_A Values(29, 'A29')
insert into Table_A Values(30, 'A30')
insert into Table_A Values(31, 'A31')go
select * from Table_A现在想要的结果是Co1 Col2 Col3 Col30
A1 A2 A3 A30谢谢
select [1] 'Co1',[2] 'Co2',[3] 'Co3',[4] 'Co4',[5] 'Co5',[6] 'Co6',[7] 'Co7',[8] 'Co8',[9] 'Co9',[10] 'Co10',
[11] 'Co11',[12] 'Co12',[13] 'Co13',[14] 'Co14',[15] 'Co15',[16] 'Co16',[17] 'Co17',[18] 'Co18',[19] 'Co19',[20] 'Co20',
[21] 'Co21',[22] 'Co22',[23] 'Co23',[24] 'Co24',[25] 'Co25',[26] 'Co26',[27] 'Co27',[28] 'Co28',[29] 'Co29',[30] 'Co30'
from
(select rn,Name
from
(select ID,
Name,
row_number() over(order by ID) 'rn'
from Table_A) t
where rn<=30
) u
pivot(max(Name) for rn in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30])) p/*
Co1 Co2 Co3 Co4 Co5 Co6 Co7 Co8 Co9 Co10 Co11 Co12 Co13 Co14 Co15 Co16 Co17 Co18 Co19 Co20 Co21 Co22 Co23 Co24 Co25 Co26 Co27 Co28 Co29 Co30
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 A11 A12 A13 A14 A15 A16 A17 A18 A19 A20 A21 A22 A23 A24 A25 A26 A27 A28 A29 A30(1 row(s) affected)
*/