Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

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
</pre>

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 (&#39;AA1|BBB1|CCCC1|DDD1|EEEE1&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;AAA2|BBB2|CC2|DDD2|EEEE2&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;AAAAA3|BBB3|CCCC3|DDD3|EEEE3&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;AAA4|BBB4|CCCC4|DD4|EEEE4&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;A5|BBB5|CCCC5|DDD5|E5&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;AAA6|BBB6|CCCC6|DDD6|EEEE6&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;AAA7|BBB7|CCCCC7|DDD7|EEEE7&#39;, Null,Null,Null,Null,Null)
 Insert Into myTable Values (&#39;AA8|BBBBBBB8|CCCC8|DDD8|EEEE8&#39;, Null,Null,Null,Null,Null)

 declare @i1 int
 declare @i2 int
 declare @i3 int
 declare @i4 int

 update myTable 
 set    @i1 = charindex(&#39;|&#39;, myCol),
        col1 = left(myCol, @i1-1),
        @i2 = charindex(&#39;|&#39;,myCol,@i1+1),
        col2 = substring(myCol, @i1+1, @i2-@i1-1),
        @i3 = charindex(&#39;|&#39;,myCol, @i2+1),
        col3 = substring(myCol, @i2+1, @i3-@i2-1),
        @i4 = charindex(&#39;|&#39;,myCol, @i3+1),
        col4 = substring(myCol, @i3+1, @i4-@i3-1),
        col5 = substring(myCol, @i4+1, 50)
 select * from myTable
</pre>

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
</pre>

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 &gt;= b.RecCount) as Rank
from    @data a
</pre>

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())
 </pre>

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) &gt; 0
select col from @mytable
select @x as positive_product
</pre>

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
</pre>


Legacy Comments


Madhivanan
2005-08-06
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

Cathy
2005-08-19
re: Begginer questions frequently asked... and answered...
Is it possible to store a .pdf file in a SQL table

Mladen
2005-08-19
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.

Jeff
2005-11-04
re: Begginer questions frequently asked... and answered...
Hey, you spelt "beginner" wrong!

Mladen
2005-11-04
re: Beginner questions frequently asked... and answered...
thanx. fixed!

Madhivanan
2005-11-18
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

Mladen
2005-11-18
re: Beginner questions frequently asked... and answered...
fixed. thanx.

Kirsten
2006-09-19
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?

Mladen
2006-09-19
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

SANDEEP
2007-06-25
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



Mladen
2007-06-25
re: Beginner questions frequently asked... and answered...
i have no idea. try asking in the forums

Mladen
2007-10-01
re: Beginner questions frequently asked... and answered...
can you provide an example?
you might be better off asking this in the forums of course :)

Madhivanan
2007-11-13
re: Beginner questions frequently asked... and answered...
To bill
http://www.sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

robyn
2008-07-14
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

gever
2009-04-20
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.

chicago
2009-10-08
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?

gopi
2010-03-08
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