I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Beginner questions frequently asked... and answered...

This is a list of some stuff (mostly beginner) i've learned here on SQLTeam forums  and i've written them down over time... i've posted it in forums here and Madhivanan gave the idea to blog it... so i did. :)) maybe this will help in the planned beginners forum :))

1. How to update a column with incrementing numbers:
    -- whole table (identity stlye) 
    declare @table1 table (id int, name varchar(50))
    insert into @table1
    select null, 'text1' union all
    select null, 'text2' union all
    select null, 'text3' union all
    select null, 'text4'
    select * from @table1 
        declare @inc int
        set @inc = 0
        UPDATE @table1 SET @inc = id = @inc + 1
    select * from @table1 
    go

    -- groups of data:
    declare @table table (id int, diag int, count1 int, rank int)
    insert into @table
    select 1, 42, 75, null union all
    select 1, 49, 50, null union all 
    select 1, 38, 22, null union all
    select 2, 70, 48, null union all
    select 2, 33, 27, null union all
    select 2, 30, 12, null union all
    select 2, 34, 5, null union all
    select 2, 54, 3, null union all
    select 3, 42, 75, null union all
    select 3, 49, 50, null union all 
    select 3, 38, 22, null 
    declare @cnt int
    set @cnt = 0
    UPDATE  t1
    SET  @cnt = rank = 
             case when 
                 exists (select top 1 id from @table where id t1.count1)    
                     then 1
             else 
                     @cnt + 1
             end
    from @table t1
    select * from @table
    
2. How to get top N rows for each group?
    Declare @myTable table (cat varchar(100), subcat varchar(100), rank int )
    Insert Into @myTable 
    Select 'A', 'a', 2 
    Union All Select 'A', 'b', 52 
    Union All Select 'A', 'c', 14 
    Union All Select 'A', 'd', 46
    Union All Select 'A', 'e', 37 
    Union All Select 'A', 'f', 95 
    Union All Select 'A', 'g', 73 
    Union All Select 'A', 'h', 67 
    Union All Select 'A', 'i', 80 
    Union All Select 'A', 'j', 03 
    Union All Select 'B', 'a', 18 
    Union All Select 'B', 'b', 44 
    Union All Select 'B', 'c', 52 
    Union All Select 'B', 'd', 60 
    Union All Select 'B', 'e', 28 
    Union All Select 'B', 'f', 06 
    Union All Select 'B', 'g', 70 
    Union All Select 'B', 'h', 90 
    Union All Select 'B', 'i', 89 
    Union All Select 'B', 'j', 31
    declare @n int
    Set @n = 5
    Select Cat, subCat, rank
    From @myTable as A
    Where (Select count(1) From @myTable Where cat=A.cat and rank <= A.rank)<=@n
    Order By Cat, Rank Desc 
    
3. How to pivot/cross tab/transpose data?
    Jeff's great stuff on pivoting    
4. How to make a search with multiple optional arguments?
    SELECT  ... 
    FROM    ...
    WHERE      (((Col1 = @Col1) or (@Col1 is null)) 
            and ((Col2 = @Col2) or (@Col2 is null))
            and ((Col3 = @Col3) or (@Col3 is null)) and ...) 
            and (other conditions)
    
5. How to put a column into a delimited form?
    use northwind
    Declare @ColumnList varchar(1000)
    SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name 
    FROM INFORMATION_SCHEMA.Columns
    WHERE table_name = 'Orders' 
    
6. How to export results of a stored procedure to a txt file?
    Run this in the DOS command line (arguments are case sensitive)     
    osql /U sa /P password /d pubs /S Server9 /Q "sp_help" -o ofile.txt
    
7. How to reset an identity column?
     DBCC CHECKIDENT('TableName', RESEED, 'StartValue')
     --or
     Truncate table TableName
     
8. Parsing delimited words from a column:
     Create table myTable (myCol varchar(50), Col1 varchar(10), Col2 varchar(10), 
                           col3 varchar(10), col4 varchar(10), col5 varchar(10))
    
     Insert Into myTable Values ('AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null)
     Insert Into myTable Values ('AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null)
    
     declare @i1 int
     declare @i2 int
     declare @i3 int
     declare @i4 int
    
     update myTable 
     set    @i1 = charindex('|', myCol),
            col1 = left(myCol, @i1-1),
            @i2 = charindex('|',myCol,@i1+1),
            col2 = substring(myCol, @i1+1, @i2-@i1-1),
            @i3 = charindex('|',myCol, @i2+1),
            col3 = substring(myCol, @i2+1, @i3-@i2-1),
            @i4 = charindex('|',myCol, @i3+1),
            col4 = substring(myCol, @i3+1, @i4-@i3-1),
            col5 = substring(myCol, @i4+1, 50)
     select * from myTable
    
9. How to get N-th max value?
 
    delcare @N int
    set @N = 5
    Select  * 
    From      Employee E1 
    Where      (@N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
    
10. How to use a variable in an IN part of where clause?
    -- either this ... 
    declare @values nvarchar(50)
    set @values = ',2,3,4,'
    select * from Table1 WHERE charindex(',' + TableId + ',', @values) > 0
    
    -- ... or use dbo.Split() function (point 18)
11. How to get a random row from a table?
    select top 1 columns....
    from table
    order by newid()
    
    -- choosing between first 20 rows
    set ROWCOUNT 20
    select top 1 *
    from (Select * from table)
    order by newid()
    set ROWCOUNT 0
    
12. How to LTrim any character from a value?
    declare @mystring varchar(8000), @chars varchar(20)
    select @mystring = 'someStringText  ', @chars = ' '
    select SUBSTRING(@mystring, CHARINDEX(LEFT(REPLACE(@mystring, @chars, ''),1), @mystring), LEN(@mystring))
    
13. How to dynamicaly rank rows?
    select     rank=count(*), a1.au_lname, a1.au_fname
    from     authors a1, authors a2
    where     a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
    group by a1.au_lname, a1.au_fname
    order by 1

    -- groups:
    Declare @data table(idn int, diag int, recCount int)
    insert into @data
    Select 1, 42, 75 union
    Select 1, 49, 50 union
    Select 1, 38, 22 union
    Select 2, 70, 48 union
    Select 2, 33, 27
    
    select  a.*,
            (select count(*) 
            from @data b 
            where a.idn = b.idn and a.RecCount >= b.RecCount) as Rank
    from    @data a
    
14. How to get a running total (value in current row = sum of all previous values)?
    SELECT     DayCount, Sales, Sales+COALESCE((SELECT SUM(Sales) 
                                             FROM     Sales b 
                                             WHERE     b.DayCount < a.DayCount),0)    AS RunningTotal
    FROM     Sales a
    ORDER BY DayCount
    
15. How to get just date or just time from a datetime value?
     -- just date:  
     SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
     
     -- just time: 
     SELECT DATEADD(d, -DATEDIFF(d, 0, GetDate()), GetDate())
     
16. how to get a number of repeating chars in a string?
    select len(@str) - len(replace(@str, @delimiter, ''))
    
17. How to multiply all of the values in a column?
    set nocount on
    declare @mytable table (col smallint)
    insert @mytable(col) select 6 union select -7 union select 7 union select null union select 2
    
    SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(nullif(col,0))))),0),0) AS bigint) AS output_value FROM @mytable
    -- or    
    declare @x bigint
    set @x = 1
    select @x = @x * col from @mytable where coalesce(col,0) > 0
    select col from @mytable
    select @x as positive_product
    
18. Split function:
    CREATE FUNCTION dbo.Split
    (
        @RowData nvarchar(2000),
        @SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (
        Id int identity(1,1),
        Data nvarchar(100)
    ) 
    AS  
    BEGIN 
        While (Charindex ( @SplitOn,@RowData)>0)
        Begin
            Insert Into @RtnValue (data)
            Select Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))
            Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
        End
    
        Insert Into @RtnValue (data)
        Select Data = ltrim(rtrim(@RowData))
    Return
    END
    

Print | posted on Monday, August 01, 2005 11:34 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Begginer questions frequently asked... and answered...

Mladen, these are very very useful tips and have answers for most asked questions in the forums. Expecting more tips from you
8/6/2005 8:19 AM | Madhivanan
Gravatar

# re: Begginer questions frequently asked... and answered...

Is it possible to store a .pdf file in a SQL table
8/19/2005 8:55 PM | Cathy
Gravatar

# re: Begginer questions frequently asked... and answered...

it's possible to store any type of file in the image datatype column.
However this is not reccomended.
the "proper way" of doing it is to store the file on the disk and save the path to it in the table.
8/19/2005 10:38 PM | Mladen
Gravatar

# re: Begginer questions frequently asked... and answered...

Hey, you spelt "beginner" wrong!
11/4/2005 6:01 PM | Jeff
Gravatar

# re: Beginner questions frequently asked... and answered...

thanx. fixed!
11/4/2005 11:02 PM | Mladen
Gravatar

# re: Beginner questions frequently asked... and answered...

In point 1, the update statement should be

UPDATE t1
SET @cnt = rank = case when
exists (select top 1 id from @table where
not exists (select top 1 id from @table where id=t1.id and count1 > t1.count1))
then 1
else @cnt + 1
end
from @table t1

I found this error when I referred this blog at this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=57901
11/18/2005 9:14 AM | Madhivanan
Gravatar

# re: Beginner questions frequently asked... and answered...

fixed. thanx.
11/18/2005 11:22 AM | Mladen
Gravatar

# Interesting Finds

5/17/2006 10:44 AM | Jason Haley
Gravatar

# re: Beginner questions frequently asked... and answered...

Hi, I'm trying to create a table with 2 field names, LCODE VARCHAR2(5 BYTE) & LVALUE VARCHAR2(40 BYTE) from an existing table which has LCODE VARCHAR2(10 BYTE) & LVALUE VARCHAR2(40 BYTE). Some of the LCODE values in the original table are of 10 characters however they are all 0's and i want to get rid of them. How do i create this duplicate table and get rid of the 0's in front?
9/19/2006 6:54 AM | Kirsten
Gravatar

# re: Beginner questions frequently asked... and answered...

I see that you're dealing with Oracle which i know nothing about.

in sql server you could do:
select replace(YourColumnName, '0', ''), otherColumns
into NewTable
From OldTable
9/19/2006 10:46 AM | Mladen
Gravatar

# re: Beginner questions frequently asked... and answered...

-----------------Crystal Report 8.0--------------

1. how to pass the parameter to Crystal report if i create a datatime paramter in crystal report 8.0 with VB6.0.

2. how to continue a coloumn on the same page after finishing the rows in same page if I have a single coloumn in the crystal report 8.0

like: -
S.No S.No.
1 8
2 9
3 10
4 11
5 12
6 13
7 14


6/25/2007 8:04 AM | SANDEEP
Gravatar

# re: Beginner questions frequently asked... and answered...

i have no idea. try asking in the forums
6/25/2007 12:44 PM | Mladen
Gravatar

# re: Beginner questions frequently asked... and answered...

can you provide an example?
you might be better off asking this in the forums of course :)
10/1/2007 7:41 AM | Mladen
Gravatar

# re: Beginner questions frequently asked... and answered...

To bill
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx
11/13/2007 10:27 AM | Madhivanan
Gravatar

# re: Beginner questions frequently asked... and answered...

PLEASE HELP! I need help with this SQL query. I need to place a command within this for the user to beable to specify a time range along with the date range that's already in this. Can anyone help me.


SELECT tblTicketsRow.fldCategory AS [Category],tblTicketsRow.fldTicketID AS [Ticket ID],tblTicketsSummary.fldDateClosed AS [Date Closed],tblTicketsRow.fldEmployeeName AS [Employee],tblTicketsRow.fldID AS [Service ID],tblTicketsRow.fldDescription AS [Description],tblTicketsRow.fldRowTotal AS [Total] FROM (tblTicketsSummary INNER JOIN tblTicketsRow ON tblTicketsSummary.fldTicketID=tblTicketsRow.fldTicketID) WHERE (tblTicketsSummary.fldDateVoided IS NULL) AND tblTicketsRow.fldPS='S' AND NOT (tblTicketsSummary.fldDateClosed IS NULL) GetUserDate('AND','tblTicketsSummary.fldDateClosed','') ORDER BY tblTicketsRow.fldCategory,tblTicketsRow.fldTicketID
7/14/2008 11:17 PM | robyn
Gravatar

# re: Beginner questions frequently asked... and answered...

Try dynamic crystal report style - check the following link

http://csharp.net-informations.com/crystal-reports/csharp-dynamic-crystal-reports.htm

gever.
4/20/2009 10:40 AM | gever
Gravatar

# re: Beginner questions frequently asked... and answered...

How do you write a query where you can retreive data from the past yr.
Lets day your fiscal yr is from July 2008 - July 2009?
10/8/2009 1:46 AM | chicago
Gravatar

# re: Beginner questions frequently asked... and answered...

how to write query that displays employees last name with first letter capitalised and all other letters lowercase,and the length of name ,for all employees whose name starts with j,a, or m.give each column an appropriate label.Sort results by employees last name
3/8/2010 8:10 PM | gopi
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET