编程地创建表 [英] creating a table progmatically

问题描述

大家好，

我想寻求有关此问题的帮助.我想创建一个具有多行&列.

我想完全像这样显示表格:

Hi all,

I want to ask a help on this problem.i want to create a table that has multiple rows & columns.

I want to display the table exactly like this:

                                                EMU   Hybrid
Available @1500hrs                                 22   22
Available @0600hrs                                 2     2
KPI                                               40    2

数据库:

database:

CREATE TABLE [dbo].[emu_hybrid](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[id_class] [int] NULL,
	[id_class_emu_hybrid] [int] NULL,
	[avail] [int] NULL,
	[total_emu] [int] NULL,
	[total_hybrid] [int] NULL,
	[kpi_emu] [int] NULL,
	[kpi_hybrid] [int] NULL,
	[date] [datetime] NULL,
 CONSTRAINT [PK_emu_hybrid] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [OMC]
GO
ALTER TABLE [dbo].[emu_hybrid]  WITH CHECK ADD FOREIGN KEY([id_class])
REFERENCES [dbo].[class_availability] ([id_class])
GO
ALTER TABLE [dbo].[emu_hybrid]  WITH CHECK ADD FOREIGN KEY([id_class_emu_hybrid])
REFERENCES [dbo].[class_emu_hybrid] ([id_class_emu_hybrid])

我已经设法创建第一列.但是我不知道如何添加第二列.这是我的编码:

I already manage to create the first column.but i dont know how to add the 2nd column.this is my coding:

StringBuilder sqlquery6 = new StringBuilder();
        sqlquery6.Append("Select 'Available @1500hrs yesterday' as ' ', sum(avail) as 'EMU' from emu_hybrid where id_class_emu_hybrid between 1 and 3 intersect select avail from emu_hybrid where id_class_emu_hybrid=4 and date='" + TextBox1.Text + "' ");
        sqlquery6.Append("UNION ");
        sqlquery6.Append("UNION ALL ");
        sqlquery6.Append("Select 'KPI' as ' ', sum(kpi_emu) as 'EMU' FROM emu_hybrid where date='" + TextBox1.Text + "'");
        

        SqlDataAdapter sqlcom20 = new SqlDataAdapter(sqlquery6.ToString(), myConnection);
        DataSet ds20 = new DataSet();
        sqlcom20.Fill(ds20);
        GridView11.DataSource = ds20.Tables[0].DefaultView;
        GridView11.DataBind();

预先感谢，
musiw.

thanks in advance,
musiw.

推荐答案

我认为在"Available @ 1600hrs"之间添加一个选择查询非常简单，不是吗?

见下文

I think it is very simple to add one more select query in between for "Available @1600hrs" isn''t it ?

See below

StringBuilder sqlquery6 = new StringBuilder();
        sqlquery6.Append("Select 'Available @1500hrs yesterday' as ' ', sum(avail) as 'EMU' from emu_hybrid where id_class_emu_hybrid between 1 and 3 intersect select avail from emu_hybrid where id_class_emu_hybrid=4 and date='" + TextBox1.Text + "' ");
        sqlquery6.Append("UNION ");
        sqlquery6.Append("Select 'Available @1600hrs yesterday' as ' ', sum(avail) as 'EMU' from emu_hybrid where id_class_emu_hybrid between 4 and 6 intersect select avail from emu_hybrid where id_class_emu_hybrid=6 and date='" + TextBox1.Text + "' ");

        sqlquery6.Append("UNION ALL ");

        sqlquery6.Append("Select 'KPI' as ' ', sum(kpi_emu) as 'EMU' FROM emu_hybrid where date='" + TextBox1.Text + "'");
        
 
        SqlDataAdapter sqlcom20 = new SqlDataAdapter(sqlquery6.ToString(), myConnection);
        DataSet ds20 = new DataSet();
        sqlcom20.Fill(ds20);
        GridView11.DataSource = ds20.Tables[0].DefaultView;
        GridView11.DataBind();

这篇关于编程地创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！