动态行转列

[chris_126]

表1
fentry fbillno value(字符型)
1 B01 test1
2 B01 test2
3 B01 test3
1 B02 H_test1
2 B02 H_test2

目标表
fbillno 1 2 3
B01 test1 test2 test3
B02 H_test1 H_test2

[gw6328]

create TABLE #t(fentry INT,fbillno VARCHAR(10),VALUE VARCHAR(10));
INSERT INTO #T (fentry,fbillno,value) VALUES 
(1 ,'B01','test1'  ),
(2 ,'B01','test2'  ),
(3 ,'B01','test '  ),
(1 ,'B02','H_test1'),
(2 ,'B02','H_test2');

--SELECT * FROM #t;

DECLARE @sql NVARCHAR(MAX);
DECLARE @s NVARCHAR(MAX);

set @s=STUFF((SELECT ',['+CAST(fentry AS VARCHAR(5))+']' FROM #t GROUP BY fentry FOR XML PATH('')),1,1,'');

SET @sql='select fbillno,'+@s+' from #t pivot (max(value) for fentry in ('+@s+')) pt';
EXEC(@sql);

DROP TABLE #t;

/*
B01	test1	test2	test 
B02	H_test1	H_test2	NULL
*/

[aaaa1185164]

额，我也在弄这个，受教了