Copy query result to Excel with Column headers in SQL Server

In SQL Server Management Studio: Tools > Options > Query Results> SQL Server > Results to Grid > Include column headers when copying and saving results (Check this option).

Another option (tip) is to show query in another tab: 

Tools > Options > Query Results> SQL Server > Results to Grid >Display Results in a separate tab (Check this option).

Advertisements

UDF to remove text from mixed string in SQL

 

CREATE function [dbo].[fn_KeepNum](@mixedNumStr varchar(8000))

returns varchar(8000) as

BEGIN

BEGIN

WHILE PATINDEX(‘%[^0-9]%’,@mixedNumStr)> 0

SET @mixedNumStr = REPLACE(@mixedNumStr,SUBSTRING(@mixedNumStr,PATINDEX(‘%[^0-9]%’,@mixedNumStr),1),”)

END

return @mixedNumStr

END

–A sample to use UDF fn_KeepNum

SELECT * FROM dbo.aTable

WHERE dbo.fn_KeepNum(charNumColumn)>=900


Remove numbers from mixed string in SQL

DECLARE @mixedNumStr varchar(500)

SET

@mixedNumStr = ‘FOSTER,ROBERT:3244472~SMITH,PATRICK A:39766516~WINDER,BARRY L:3246852~MEYER,JIM W:3256885~HOLD,JERALD:3278662~LEAMAN,MICHAEL P:31555’;

BEGIN

WHILE

PATINDEX(‘%[0-9]%’,@mixedNumStr)> 0

SET

@mixedNumStr = Replace(REPLACE(REPLACE(@mixedNumStr,SUBSTRING(@mixedNumStr,PATINDEX(‘%[0-9]%’,@mixedNumStr),1),),‘~’,‘ / ‘),‘:’,)

END

PRINT

@mixedNumStr


Sample for Aggregate Window functions in SQL Server 2005 and 2008

Over clause can apply to Window functions in SQL server 2005 and 2008. The basic syntax is:
Ranking Window Functions 
<OVER_CLAUSE> :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions 
<OVER_CLAUSE> :: = 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
--From books online
It is required to have an ORDER BY clause for Ranking Window functions, for example, ROW_NUMBER() OVER(ORDER BY ID). PARTITION BY is optional.
Here are two samples for Aggregate Window Functions( adopted from Books Online)
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Total’
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Avg’
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Count’
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Min’
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS ‘Max’
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43664;
GO
--And another one with calculation:
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) 
        *100 AS DECIMAL(5,2))AS 'Percent by ProductID'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID =43659;
GO
 
 

Cascading DropDownList on GridView with Editing

Suprotim Agarwal posted a cascading dropdowonlist with gridview on this http://www.dotnetcurry.com/ShowArticle.aspx?ID=221

The key is to use FilterExpression. It is handy without any code needed. However, it will be more useful if the solution can apply to update gridview for the cascading dropdownlist. Sadly, we still need some code to handle the update values from these dropdownlist.

Here is a sample for the implementation:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" OnRowUpdating="GridView1_RowUpdating"

AutoGenerateColumns="false" DataKeyNames="CustomerId" >

<Columns>

<asp:CommandField ShowEditButton="True" />

<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

<asp:TemplateField HeaderText="Manufacture" SortExpression="Manufacture">

<EditItemTemplate>

<asp:DropDownList ID="ddlManufacture" runat="server" AutoPostBack="True" DataTextField="Manufacture"

DataValueField="Manufacture" Width="122px" SelectedValue=’<%# Bind("Manufacture") %>

DataSourceID="SqlDataSource2" >

</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>"

SelectCommand="SELECT [Manufacture] FROM Manufactures"></asp:SqlDataSource>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID="Label2" runat="server" Text=’<%# Bind("Manufacture") %>‘></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Model" SortExpression="Model">

<EditItemTemplate>

Model:

<asp:DropDownList ID="ddlModel" runat="server" DataSourceID="SqlDataSource3" DataTextField="Model" DataValueField="Model" >

</asp:DropDownList>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="ddlModel" InitialValue="" ErrorMessage="Select a Model" ></asp:RequiredFieldValidator>

<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>"

SelectCommand="SELECT [Model],[Manufacture] FROM [Models] " FilterExpression="[Manufacture]= ‘{0}’">

<FilterParameters><asp:ControlParameter ControlID="ddlManufacture" Name="Manufacture" PropertyName="SelectedValue" Type="String" />

</FilterParameters>

</asp:SqlDataSource>

</EditItemTemplate>

<ItemTemplate>

<asp:Label ID="Label1" runat="server" Text=’<%# Bind("Model") %>‘></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

DeleteCommand="DELETE FROM [Customers] WHERE [CustomerId] = @CustomerId"

InsertCommand="INSERT INTO [Customers] ([Name], [Manufacture], [Model]) VALUES (@Name, @Manufacturer, @Model)"

SelectCommand="SELECT [CustomerId], [Name], [Manufacture], [Model] FROM [Customers] WHERE [Manufacture] IS NOT NULL AND [Model] IS NOT NULL"

UpdateCommand="UPDATE [Customers] SET [Name] = @Name, [Manufacture] = @Manufacture, [Model] = @Model WHERE [CustomerId] = @CustomerId">

<DeleteParameters>

<asp:Parameter Name="CustomerId" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource>

protected

void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

//string strModel = ((DropDownList)((GridView)sender).Rows[e.RowIndex].FindControl("ddlModel")).SelectedItem.Value;

string strModel = ((DropDownList)((GridView)sender).Rows[e.RowIndex].FindControl("ddlModel")).SelectedValue;

string strManufacturer = ((DropDownList)((GridView)sender).Rows[e.RowIndex].FindControl("ddlManufacture")).SelectedItem.Value;

e.NewValues[

"Model"] = strModel;

e.NewValues[

"Manufacture"] = strManufacturer;

e.Cancel =

false;

GridView gv = (GridView)sender;

for (int i = 0; i < gv.Columns.Count; i++)

{

DataControlFieldCell cell = gv.Rows[e.RowIndex].Cells[i] as DataControlFieldCell;

gv.Columns[i].ExtractValuesFromCell(e.NewValues, cell,

DataControlRowState.Edit, true);

}

 

}


Running total for group (SQL Server)

Here is an question about running total: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=4136119&SiteID=1&mode=1

 

CREATE TABLE #temp(col1 char(1),col2 int)

insert

into #temp

select

‘A’, 100

union all

select ‘A’, 200

union all

select ‘A’, 300

union all

select ‘B’, 400

union all

select ‘B’, 500

union all

select ‘C’, 600

select

a.col1, a.col2, SUM(b.col2) as gTotal FROM #temp a

cross

join #temp b

WHERE

a.col1=b.col1 and b.col2<=a.col2

GROUP

BY a.col1,a.col2

ORDER

BY a.col1,a.col2

 –Or

SELECT

col1, col2, (SELECT SUM(b.col2) FROM #temp b WHERE a.col1=b.col1 AND b.col2 <= a.col2) AS gTotal FROM #temp AS a

ORDER

BY col1, col2;

 

drop

table #temp


Editing for GridView and DetailsView with Access 2007

Here is a sample gridview and detailsview with editing capability with Access 2007 data. The key is to connect to the Access 2007 database table.

Connection string in your webconfig:

<connectionStrings>

<

add name="Access2007ConnectionString" connectionString="Dsn=MS Access Database;dbq=|DataDirectory|\Database1.accdb;" providerName="System.Data.Odbc" />

</

connectionStrings>

FYI: You can try another way to connect to Access 2007 in your code if you choose to:

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(".") + "\\App_Data\\Database1.accdb;" ;

Here is the sample code:

   <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="CartridgeName">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="CartridgeName" HeaderText="CartridgeName" ReadOnly="True"
                    SortExpression="CartridgeName" />
                <asp:BoundField DataField="UnitsInstock" HeaderText="UnitsInstock" SortExpression="UnitsInstock" />
                <asp:BoundField DataField="UnitsNeeded" HeaderText="UnitsNeeded" SortExpression="UnitsNeeded" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Access2007ConnectionString %>"
            DeleteCommand="DELETE FROM [Cartridge] WHERE ([CartridgeName] = ?)"
            InsertCommand="INSERT INTO [Cartridge] ([CartridgeName], [UnitsInstock], [UnitsNeeded]) VALUES (?, ?, ?)"
            ProviderName="<%$ ConnectionStrings:Access2007ConnectionString.ProviderName %>"
            SelectCommand="SELECT [CartridgeName], [UnitsInstock], [UnitsNeeded] FROM [Cartridge]"
            UpdateCommand="UPDATE [Cartridge] SET [UnitsInstock] = ?, [UnitsNeeded] = ? WHERE ([CartridgeName] = ?)">
            <DeleteParameters>
                <asp:Parameter Name="CartridgeName" Type="String" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="UnitsInstock" Type="String" />
                <asp:Parameter Name="UnitsNeeded" Type="String" />
                <asp:Parameter Name="CartridgeName" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="CartridgeName" Type="String" />
                <asp:Parameter Name="UnitsInstock" Type="String" />
                <asp:Parameter Name="UnitsNeeded" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
        <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AllowPaging="True" DataSourceID="SqlDataSource1">
            <Fields>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
This is to answer a question here:
http://forums.asp.net/t/1347791.aspx