行转列

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void sp_execsql (string sql)
    {
        using (SqlConnection cn = new SqlConnection("context connection=true"))
        {
            SqlDataRecord sqldr;
            DataSet dataSet = null;
            int i = 0;
            sqldr = new SqlDataRecord(new SqlMetaData[]{
                    new SqlMetaData("TableName",SqlDbType.VarChar,255),
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("Field",SqlDbType.VarChar,255),
                    new SqlMetaData("Value",SqlDbType.NVarChar,4000)
                    });

            SqlDataAdapter adapter = new SqlDataAdapter(sql, cn)
            {
                SelectCommand = { CommandType = CommandType.Text }
            };
           
            dataSet = new DataSet();
            adapter.Fill(dataSet);

            SqlContext.Pipe.SendResultsStart(sqldr);
            foreach (DataTable dt in dataSet.Tables)
            {
                i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    i++;
                    foreach (DataColumn col in dt.Columns)
                    {
                        sqldr.SetSqlString(0, dt.TableName);
                        sqldr.SetSqlInt32(1, i);
                        sqldr.SetSqlString(2, col.ColumnName);
                        sqldr.SetString(3, row[col.ColumnName].ToString() );
                        SqlContext.Pipe.SendResultsRow(sqldr);
                    }
                }
            }
            SqlContext.Pipe.SendResultsEnd();
        }
    }
}