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 ('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
|
|
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 |