Copy query result to Excel with Column headers in SQL Server

Posted: November 27, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment

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).

UDF to remove text from mixed string in SQL

Posted: November 24, 2008 | Author: | Filed under: SQL Server | Leave a comment

 

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

Posted: November 22, 2008 | Author: | Filed under: SQL Server | Leave a comment

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

Posted: November 18, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment
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

Posted: November 18, 2008 | Author: | Filed under: ASP.NET 2 | 7 Comments

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)

Posted: November 17, 2008 | Author: | Filed under: SQL Server | Leave a comment

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

Posted: November 13, 2008 | Author: | Filed under: Access 2007 | Leave a comment

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







		



		
		

	
Compare Dates in an iterative manner for a particular set of records –SQL Server 2005 Approach

	Posted: November 13, 2008 | Author:  | Filed under: SQL Server 2005   | Leave a comment
	


I came across an article with the title from SQL Server Central with a solution for SQL Server 2000: http://www.sqlservercentral.com/articles/T-SQL/64555/


It would be interesting to see a solution for SQL Server 2005 solution. It seems this SQL Server solution is outperforming the CROSS JOIN solution.


Here is the code:




 


;WITH mycte


AS


 


(


SELECT A.PersonID,


CASE


WHENSUM(CASEWHEN A.rn–A.Version<>0 THEN 1 ELSE 0 END)>0 THEN 1 ELSE 0 ENDas [Is ImProper]


FROM


(


SELECT


*,ROW_NUMBER()OVER(PARTITIONBY PersonID ORDERBY DEDate)-1 as rn


FROM


PersonRecord) A


GROUP


BY A.PersonID)


SELECT


B.PersonID, B.Version, B.DEDate, [Is ImProper] 


FROM


PersonRecord B LEFTJOIN mycte ON b.PersonID=mycte.PersonID




 



		



		
		

	
Dynamic PIVOT in SQL Server 2005

	Posted: November 12, 2008 | Author:  | Filed under: SQL Server 2005   | Leave a comment
	


Here is a sample with synamic script for pivot:




—-create Table yourTable4 (Row int, ID int, Poltype int) 


–INSERT INTO yourTable4 


–SELECT 1,370,103 


–UNION ALL SELECT 2,370,100 


–UNION ALL SELECT 3,370,381 


–UNION ALL SELECT 4,370,580 


–UNION ALL SELECT 1,380,384 


–UNION ALL SELECT 2,380,181 


–UNION ALL SELECT 3,380,384 


–UNION ALL SELECT 4,380,181 


— 


—-SELECT ID, [1] as Poltype1,[2] as Poltype1,[3] as Poltype3 FROM 


—-( 


—-SELECT ID, ROW, Poltype FROM yourTable4 


—- 


—-) src 


—-PIVOT (MAX(Poltype) FOR ROW IN ([1],[2],[3])) pvt 


  


  


  


SET NOCOUNT ON 


DECLARE @T AS TABLE(y int NOT NULL PRIMARY KEY) 


INSERT


 INTO @T SELECT DISTINCT ROW FROM yourTable4 






DECLARE


 @T1 AS TABLE(num int NOT NULL PRIMARY KEY) 


DECLARE


 @i AS int 


SET


 @i=1 


WHILE


 @i <20 


BEGIN 


INSERT




 INTO @T1 SELECT @i 


SET


 @i=@i+1 


END 


DECLARE


 @cols AS nvarchar(MAX), @cols2 AS nvarchar(MAX),@y AS nvarchar(20) 


SET


 @y = (SELECT MIN(y) FROM @T) 


SET


 @cols = N” 


SET


 @cols2 = N” 


WHILE


 @y IS NOT NULL 


BEGIN 


SET




 @cols = @cols + N‘,[‘+CAST(@y AS nvarchar(20))+N‘]’ 


SET


 @cols2 = @cols2 + N‘, [‘+CAST(@y AS nvarchar(20))+N‘] AS [Poltype’+CAST(@y AS nvarchar(20))+N‘]’ 


SET


 @y = (SELECT MIN(y) FROM @T WHERE y > @y) 


END 


SET




 @cols = SUBSTRING(@cols, 2, LEN(@cols)) 


SET


 @cols2 = SUBSTRING(@cols2, 2, LEN(@cols2)) 


–print @cols2 


DECLARE


 @sql AS nvarchar(MAX) 


SET


 @sql = N‘SELECT ID, ‘ +@cols2 + N‘ FROM ( SELECT ID, ROW, Poltype FROM yourTable4) src 


PIVOT (MAX(Poltype) FOR ROW IN(‘


 + @cols + N‘)) AS pvt’  


EXEC sp_executesql @sql 


  


–Another one put into a stored procedure:(http://forums.asp.net/t/1345606.aspx) 


Alter Procedure mySP_DynamicPivotSample 


@myYear int 


  


AS 


SET


 NOCOUNT ON 


BEGIN 


DECLARE


 @T AS TABLE(y int NOT NULL PRIMARY KEY) 


;


WITH mycte 


AS 


( 


SELECT


 1 as m, DATEADD(month,0,CAST(‘1/1/’+CAST(@myYear as CHAR(4)) as datetime)) as dt1 


UNION


 ALL 


SELECT


 m+1, DATEADD(month,1,dt1) as dt1 FROM mycte WHERE m<24 


)


 


INSERT


 INTO @T SELECT RIGHT(‘0’+ CAST(Month(dt1)as varchar(2)),2)+ 


CAST


(Year(dt1)as char(4)) as mymonth FROM mycte 


–SELECT * FROM @T 


DECLARE @cols AS nvarchar(MAX), @cols2 AS nvarchar(MAX),@y AS int 


SET


 @y = (SELECT MIN(y) FROM @T) 


SET


 @cols = N” 


SET


 @cols2 = N” 


WHILE


 @y IS NOT NULL 


BEGIN


 


SET @cols = @cols + N’,[‘+RIGHT(‘0’+CAST(@y AS nvarchar(10)),6)+N’]’ 


  


SET @cols2 = @cols2 + N’,ISNULL([‘+RIGHT(‘0’+CAST(@y AS nvarchar(10)),6)+N’],0) as [‘+RIGHT(‘0’+CAST(@y AS nvarchar(10)),6)+N’]’ 


SET @y = (SELECT MIN(y) FROM @T WHERE y > @y) 


END 


SET




 @cols = SUBSTRING(@cols, 2, LEN(@cols)) 


SET


 @cols2 = SUBSTRING(@cols2, 2, LEN(@cols2)) 


–print @cols2


 


DECLARE


 @sql AS nvarchar(MAX) 


SET @sql = N’SELECT Descrizione, Ordine, Tipo,’ + @cols2 + N’ 


FROM (SELECT Descrizione, Ordine, Tipo, Importo, 


RIGHT(”0”+CONVERT(varchar(2),[Mese]),2) +CONVERT(char(4),[Anno]) as MonthYear 


FROM Flussi) AS F 


PIVOT ( MAX(Importo) FOR MonthYear IN(‘






 + @cols + N’)) AS P’ 


EXEC


 sp_executesql @sql 


End









		



		
		

	
What a formula in EXCEL– PRODUCT

	Posted: November 10, 2008 | Author:  | Filed under: Excel   | Leave a comment
	


I was in a huarry to convert a unit with one thousand in Excel 2003. I used the built-in formula PRODUCT like this =PRODUCT(A2,1000) and it seems fine with the conversion. But I was caught by the value of 1000 in the converted column by surprise. Any cell without a value (blank, NULL in database term0 will have 1000 in converted cell. What a result?! Instead, I have to use A2*1000 in the cell as function to do the conversion. 


Lesson learned, do not assume anything. Double check your result before you turn it in.


 


By the way, it has the same behavior in Excel 2007.



		



		
		

			
← Older Entries