C#数据库读取数据行列转换展示问题

Question

有下面的数据库

字段B	字段A	数值
b1	a1	1
b1	a2	2
b1	a3	3
b1	a4	4
b1	a5	5
b1	a6	6
b2	a1	7
b2	a2	8
b2	a3	9
b2	a4	10
b2	a5	11
b2	a6	12
b3	a1	13
b3	a2	14
b3	a3	15
b3	a4	16
b3	a5	17
b3	a6	18

需要在C# 里面展示成下面的样子

	b1	b2	b3
a1	1	7	13
a2	2	8	14
a3	3	9	15
a4	4	10	16
a5	5	11	17
a6	6	12	18

就是把“字段B的内容作为新表的字段”，然后对应的字段A作为数值展示

不知道是用数据库语句处理后再展示，还是在C#里面处理展示。

目前没有思路，请教一下各位。

Answer 1

这是我做的简单显示程序
表格显示用的ListView：

核心SQL代码：
SELECT
A,[b1] as b1,[b2] as b2,[b3] as b3
FROM
test
PIVOT(SUM(Num) FOR [B] IN([b1],[b2],[b3])) AS T

附程序代码：

        private void Form1_Load(object sender, EventArgs e)
        {
            string sql = "select * from test";
            sf(sql);
            string sql2 = "SELECT A,[b1] as b1,[b2] as b2,[b3] as b3 FROM test PIVOT(SUM(Num) FOR[B] IN([b1], [b2], [b3])) AS T ";
            sf2(sql2);
        }

        Class1 ca = new Class1();
        DataRow[] drs;
        ListViewItem lvi;
        public void sf(string sql)
        {
            this.listView1.Items.Clear();
            DataTable dt = ca.getdt(sql);
            drs = dt.Select();
            for (int i = 0; i < drs.Count(); i++)
            {
                lvi = new ListViewItem();
                lvi.Text = "";
                lvi.SubItems.Add(drs[i][0].ToString());
                lvi.SubItems.Add(drs[i][1].ToString());
                lvi.SubItems.Add(drs[i][2].ToString());
                this.listView1.Items.Add(lvi);
            }
        }

        public void sf2(string sql)
        {
            this.listView2.Items.Clear();
            DataTable dt = ca.getdt(sql);
            drs = dt.Select();
            for (int i = 0; i < drs.Count(); i++)
            {
                lvi = new ListViewItem();
                lvi.Text = "";
                lvi.SubItems.Add(drs[i][0].ToString());
                lvi.SubItems.Add(drs[i][1].ToString());
                lvi.SubItems.Add(drs[i][2].ToString());
                lvi.SubItems.Add(drs[i][3].ToString());
                this.listView2.Items.Add(lvi);
            }
        }

如果回答帮助题主解决了问题，希望题主能点击采纳此答案。

二更：
题主问题：b1，b2，b3只是假设有这么多，实际不止。这个序列怎么弄？

private void button1_Click(object sender, EventArgs e)
        {
            var t1 = Convert.ToInt32(textBox1.Text);
            string sql = "";
            string sql2 = "";
            for (int i = 1; i <= t1; i++)
            {
                sql = sql + "[b" + i + "] as b" + i + ",";
                sql2 = sql2 + "[b" + i + "],";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql2 = sql2.Substring(0, sql2.Length - 1);
            string sql3 = "SELECT A,"+sql + " FROM test PIVOT(SUM(Num) FOR[B] IN("+sql2 + ")) AS T ";
            sf2(sql3, t1);
        }

效果图：

全部代码：

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string sql = "select * from test";
            sf(sql);
        }

        Class1 ca = new Class1();
        DataRow[] drs;
        ListViewItem lvi;
        public void sf(string sql)
        {
            this.listView1.Items.Clear();
            DataTable dt = ca.getdt(sql);
            drs = dt.Select();
            for (int i = 0; i < drs.Count(); i++)
            {
                lvi = new ListViewItem();
                lvi.Text = "";
                lvi.SubItems.Add(drs[i][0].ToString());
                lvi.SubItems.Add(drs[i][1].ToString());
                lvi.SubItems.Add(drs[i][2].ToString());
                this.listView1.Items.Add(lvi);
            }
        }

        public void sf2(string sql,int cols)
        {
            this.listView2.Items.Clear();
            DataTable dt = ca.getdt(sql);
            drs = dt.Select();
            for (int i = 0; i < drs.Count(); i++)
            {
                lvi = new ListViewItem();
                lvi.Text = "";
                for (int j = 0; j <= cols; j++)
                {
                    lvi.SubItems.Add(drs[i][j].ToString());
                }
                this.listView2.Items.Add(lvi);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var t1 = Convert.ToInt32(textBox1.Text);
            string sql = "";
            string sql2 = "";
            for (int i = 1; i <= t1; i++)
            {
                sql = sql + "[b" + i + "] as b" + i + ",";
                sql2 = sql2 + "[b" + i + "],";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql2 = sql2.Substring(0, sql2.Length - 1);
            string sql3 = "SELECT A,"+sql + " FROM test PIVOT(SUM(Num) FOR[B] IN("+sql2 + ")) AS T ";
            sf2(sql3, t1);
        }
    }

如果方案解决了问题，希望题主能采纳此答案，您的采纳是我们回答的动力。