2016/04/28
insert into select插入数据 使用方法
http://dotnet.9sssd.com/aspnet/art/806
VB 给下dropdownlist设置值
设置数据表选中的值： dropdowslist.selectedIndex=-1 dropdownlist.Items.findbyvalue(你的值).selected=true 或 dropdownlist.Items.findbytext(你的值).selected=true for example: drop1.Items.FindByValue("5").Selected=true; drop1.Items...
行列转换
--select * from ( select row_number() OVER () as rowno,SS.* from (
declare @sql varchar(8000)
select @sql= isnull(@sql+',','')+' max(case ProdBigTypeName when '''+ ProdBigTypeName +''' then LeftAmount else 0 end) ['+ ProdBigTypeName +']'
from(select distinct ProdBigTypeName from
(select m.ProductLineCD ,g.ProdBigTypeName ,m.CustCD ,j.CustNameCn,SUM(m.LeftAmount ) LeftAmount
from(select d.ProductCD,d.CustCD ,d.ProductLineCD ,isnull(d.onhand,0) - isnull(c.allocationqty,0) LeftQty
, isnull(d.Amount,0) - isnull(c.allocationamount,0) LeftAmount
from(select b.ProductLineCD,a.OverstockNO,a.CustCD, b.ProductCD,b.Onhand,b.Price,b.Amount,b.ProductDate
from Overstock a,OverstockDtl b where a.OverstockNO =b.OverstockNO and (a.Status ='N' or a.Status ='P'))d
left join (select OverstockNO,ProductCD,ProductDate ,SUM(isnull(allocationqty,0)) allocationqty ,SUM(isnull(allocationamount,0)) allocationamount
from Allocation group by OverstockNO,ProductCD,ProductDate ) c on d.OverstockNO =c.OverstockNO and d.ProductCD =c.ProductCD and d.ProductDate = c.ProductDate ) m
left join Product f on m.ProductCD = f.ProdID left join ProdBigType g on g.ProdBigTypeID = f.ProdBigTypeID
left join Customer j on j.CustID = m.CustCD group by m.ProductLineCD,m.CustCD,j.CustNameCn,g.ProdBigTypeName) as LLL ) asa
set @sql ='select CustNameCn,'+@sql+' from (select m.ProductLineCD ,g.ProdBigTypeName ,m.CustCD ,j.CustNameCn,SUM(m.LeftAmount ) LeftAmount
from(select d.ProductCD,d.CustCD ,d.ProductLineCD ,isnull(d.onhand,0) - isnull(c.allocationqty,0) LeftQty
, isnull(d.Amount,0) - isnull(c.allocationamount,0) LeftAmount from(select b.ProductLineCD,a.OverstockNO,a.CustCD, b.ProductCD,b.Onhand,b.Price,b.Amount,b.ProductDate
from Overstock a,OverstockDtl b where a.OverstockNO =b.OverstockNO and (a.Status = ''N'' or a.Status = ''P''))d
left join (select OverstockNO,ProductCD,ProductDate ,SUM(isnull(allocationqty,0)) allocationqty ,SUM(isnull(allocationamount,0)) allocationamount from Allocation
group by OverstockNO,ProductCD,ProductDate ) c on d.OverstockNO =c.OverstockNO and d.ProductCD =c.ProductCD and d.ProductDate = c.ProductDate ) m
left join Product f on m.ProductCD = f.ProdID left join ProdBigType g on g.ProdBigTypeID = f.ProdBigTypeID left join Customer j on j.CustID = m.CustCD
group by m.ProductLineCD,m.CustCD,j.CustNameCn,g.ProdBigTypeName) as LLL group by CustNameCn'
exec(@sql)
--) SS ) FF where 1=1 and FF.rowno >= 1 and FF.rowno < 26 order by FF.rowno
vb
Private Function ConvertDataTable(ByRef dt As DataTable) As DataTable
DeleteNullCol(dt)
'Dim dt As DataTable = New DataTable("Datas")
'dt.Columns.Add("姓名", Type.GetType("System.String"))
'dt.Columns.Add("科目", Type.GetType("System.String"))
'dt.Columns.Add("分数", Type.GetType("System.Int32"))
'dt.Rows.Add(New Object() {"张三", "语文", 89})
'dt.Rows.Add(New Object() {"张三", "数学", 90})
'dt.Rows.Add(New Object() {"张三", "英语", 79})
'dt.Rows.Add(New Object() {"张三", "地理", 70})
'dt.Rows.Add(New Object() {"张三", "生物", 95})
'dt.Rows.Add(New Object() {"李四", "语文", 87})
'dt.Rows.Add(New Object() {"李四", "英语", 86})
'dt.Rows.Add(New Object() {"李四", "地理", 82})
'dt.Rows.Add(New Object() {"王五", "语文", 81})
'dt.Rows.Add(New Object() {"王五", "数学", 70})
'dt.Rows.Add(New Object() {"王五", "英语", 88})
'dt.Rows.Add(New Object() {"王五", "生物", 96})
Dim result As DataTable = New DataTable()
result.Columns.Add(dt.Columns(1).ColumnName)
Dim dtColumns As DataTable = New DataTable()
dtColumns = dt.DefaultView.ToTable("dtColumns", True, dt.Columns(0).ColumnName.ToString())
Dim colName As String
For i As Integer = 0 To dtColumns.Rows.Count - 1
colName = dtColumns.Rows(i)(0).ToString()
result.Columns.Add(colName)
result.Columns(i + 1).DefaultValue = "0"
Next
Dim drNew As DataRow = result.NewRow()
Dim exRow() As DataRow
Dim dr As DataRow = dt.NewRow()
Dim custAfter As String = dt.Rows(0)(1).ToString()
Dim custName As String
Dim typeName As String
Dim dvalue As String
For Each dr In dt.Rows
custName = dr(1).ToString
drNew(0) = custAfter
If (custName = custAfter) Then
typeName = dr(0).ToString()
dvalue = dr(2).ToString()
drNew(typeName) = dvalue
Else
result.Rows.Add(drNew)
drNew = result.NewRow()
custName = dr(1).ToString
drNew(0) = custAfter
typeName = dr(0).ToString()
dvalue = dr(2).ToString()
drNew(typeName) = dvalue
End If
custAfter = custName
Next
result.Rows.Add(drNew)
Return result
End Function
删除空白列
Private Function DeleteNullCol(ByRef dt As DataTable) As DataTable
Dim dtRow As DataRow
dtRow = dt.NewRow()
Dim flg As Boolean
For colId As Integer = dt.Columns.Count - 1 To 0 Step (-1)
flg = True
For rowId As Integer = 0 To dt.Rows.Count - 1
If (String.IsNullOrEmpty(dt.Rows(rowId)(colId).ToString()) = False) Then
flg = False
Exit For
End If
Next
If (flg = True) Then
dt.Columns.RemoveAt(colId)
End If
Next
dt.Columns.Remove(PFCC0101Field.PRODUCTLINECD_FIELD)
dt.Columns.Remove(PFCC0101Field.CUSTCD_FIELD)
dt.Columns.Remove("ROWNO")
Return dt
End Function
