这是我做的简单显示程序
表格显示用的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);
}
}
如果方案解决了问题,希望题主能采纳此答案,您的采纳是我们回答的动力。