sql 如何动态多行转多列

Question

sql 如何动态多行转多列，只知道表名不知道表里数据的情况下

Answer 1

使用存储过程。
方法如下：
1、先查询出表里面的列名称
2、行列转换

举例
1、查询student字段名
SELECT
a.colorder as _colOrder, --字段序号
a.name as _colName --字段名
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
where d.name='student' --所要查询的表
order by a.id,a.colorder

2、行列转换
SELECT
MAX (CASE WHEN SetupID = 1 THEN SetupName ELSE NULL END ) AS [1],
MAX (CASE WHEN SetupID = 2 THEN SetupName ELSE NULL END ) AS [2],
MAX (CASE WHEN SetupID = 3 THEN SetupName ELSE NULL END ) AS [3],
MAX (CASE WHEN SetupID = 4 THEN SetupName ELSE NULL END ) AS [4]
......
FROM table

Answer 2

这个关键还得知道原来的行里列的分布吧，不然最简单的如果只有一列的话，直接行号边列名就行了呗，哈

Answer 3

写存储过程实现追问

然后呢- -