freedompangmy 2022-02-16 10:17 采纳率: 100%
浏览 63
已结题

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

有下面的数据库

字段B字段A数值
b1a11
b1a22
b1a33
b1a44
b1a55
b1a66
b2a17
b2a28
b2a39
b2a410
b2a511
b2a612
b3a113
b3a214
b3a315
b3a416
b3a517
b3a618

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

b1b2b3
a11713
a22814
a33915
a441016
a551117
a661218

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

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

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

  • 写回答

3条回答 默认 最新

  • CSDN专家-Tk 2022-02-16 11:31
    关注

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

    img

    核心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

    img

    附程序代码:

    
            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);
            }
    

    效果图:

    img

    img

    全部代码:

    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);
            }
        }
    

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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论 编辑记录
查看更多回答(2条)

报告相同问题?

问题事件

  • 系统已结题 2月24日
  • 已采纳回答 2月16日
  • 创建了问题 2月16日

悬赏问题

  • ¥15 arduino控制ps2手柄一直报错
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥85 maple软件,solve求反函数,出现rootof怎么办?
  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题