Begginer questions frequently asked... and answered...

Begginer questions frequently asked... and answered...

Nice!!!

Begginer questions frequently asked... and answered...
This is a list of some stuff (mostly begginer) i've learned here on SQLTeam forums and i've written them down over time... i've posted it in forums here and on Madhivanan gave the idea to blog it... so i did. :)) maybe this will help in the planned begginers 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
- 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 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
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?

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

4. How to make a search with multiple optional arguments?

SELECT ...
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 Nth 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?

- declare @values nvarchar(50)
set @values = ',2,3,4,'
select * from Table1 WHERE charindex(',' + TableId + ',', @values) > 0
- use dbo.Split() function

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?

- SUBSTRING(@mystring, CHARINDEX(LEFT(REPLACE(@mystring, @char, ''),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?

- SELECT CAST(ROUND(COALESCE(EXP(SUM(LOG(ABS(nullif(col,0))))),0),0) AS INTEGER) AS output_value FROM @mytable
- 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
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