NOTE: If you know the answer of this question, then I request you to stop reading this post right now. Please do not leave comment about this blog post not being useful to you, if you knew the answer.
Few days ago, I received DM asking What is an AdventureWorks database and why in all the examples I use that instead of any other database (e.g. Pubs or Northwind)? As matter of fact, when I went back to my question list, which I have yet not answered, there were a few more variations of this same question.
AdventureWorks is a Sample Database shipped with SQL Server and it can be downloaded from http://codeplex.com site.
AdventureWorks has replaced Northwind and Pubs from the sample database in SQL Server 2005.
The Microsoft team keeps updating the sample database as they release new versions.
Here are some quick links:
AdventureWorks SQL Server 2008 SR4
AdventureWorks 2008R2 November CTP
AdventureWorks for SQL Azure (December CTP)
AventureWorks for SQL Server 2005 SP2A
SQL SERVER – 2008 – Download and Install Samples Database AdventureWorks 2005 – Detail Tutorial
I have previously written few other articles on the same subject; you can find them easily here: Search@SQLAuthority.com
UPDATE: Everybody knows Buck Woody (Blog | Twitter)! Buck has written excellent reference article regarding The SQL Server Sample Databases here. I strongly suggest to check that out.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
I apparently knew only the partial answer to the question…I was not aware that DB was updated at each release.
Thanks for the information :)
Hi Pinal,
I have been reading your blogs from last three years, and learned much more from you. I really appriciate your work.
I have struck up with some situation, so i need your help:
I need query help for below senorio, could you please help me.
TableName = City
CITYID ContinuationID CITYNAME
1 1 SAN
1 2 DIEGO
2 1 SAN
2 2 FRANCISCO
3 1 CHICAGO
4 1 NEW
4 2 YORK
4 3 CITY
Could you please help me to write a generalized SQL that returns results as given below in the
Query result
CITYID NAME1 NAME2 NAME3 NAME4 NAME5
1 SAN DIEGO
2 SAN FRANCISCO
3 CHICAGO
4 NEW YORK CITY
I tried ur issue by using a cursor .
here in my example sample is my table name .The below is the code for that. Check whether it may be helpful to you or not.
Here i didn’t consider the continuation id column ..according to your requirement
GO
DECLARE @id INT,@cnt INT,@lpcnt INT,@str VARCHAR(100),@var VARCHAR(100)
SET @cnt= 0
SET @lpcnt =0
SET @str = ”
DECLARE CONCAT CURSOR FOR
SELECT DISTINCT id FROM SAMPLE
OPEN CONCAT
FETCH NEXT FROM CONCAT INTO @id
WHILE @@fetch_status =0
BEGIN
SELECT @cnt = COUNT(DISTINCT city) FROM SAMPLE WHERE id =@id
SET @lpcnt= 1
SET @str = ”
WHILE (@lpcnt <= @cnt )
BEGIN
SELECT @var=city FROM (
SELECT ROW_NUMBER () OVER (PARTITION BY id ORDER BY id,conid ) AS rowNum,id, city
FROM SAMPLE) t
WHERE rownum =@lpcnt AND id =@id
SET @str=@STR+@var
SET @lpcnt = @lpcnt + 1
END
DELETE from SAMPLE WHERE id=@id
INSERT INTO SAMPLE(id,city) VALUES(@id, @STR)
FETCH NEXT FROM CONCAT INTO @id
END
CLOSE CONCAT
DEALLOCATE CONCAT
GO
Here the o/p would be :
id continuationid city
1 NULL sandeo
2 NULL SANFRANCISCO
3 NULL CHICAGO
4 NULL NEWYORKCITY
Thank you for your help Rajesh,
It works fine , but cursors are very slow .
Similar query i have to run for Party names (like Buyer, seller etc) nearly 70,000,000 records
Can we do without using cursor?
If you have a known small maximum number of levels (ei ContinuationID) you could try the following. It should be pretty quick.
select j1.cityid, j1.CityName + isnull(‘ ‘ + j2.CityName,”) + isnull(‘ ‘ + j3.CityName,”) + isnull(‘ ‘ + j4.CityName,”) as CityName
from city j1
left outer join city j2 on j2.ContinuationId=2 and j1.cityid=j2.cityid
left outer join city j3 on j3.ContinuationId=3 and j1.cityid=j3.cityid
left outer join city j4 on j4.ContinuationId=4 and j1.cityid=j4.cityid
where j1.ContinuationId=1
Hi Patil i had tried by using xml . See whether this may solve your issue or not ?
SELECT DISTINCT Id,
City = REPLACE(
(
SELECT
City AS [data()]
FROM
Sample soh
WHERE
soh.Id = c.Id
FOR XML PATH (”)
), ‘ ‘, ‘ ‘)
FROM
Sample c
ORDER BY
Id
output :
ID City
— ———
1 sandeo France
2 SANFRANCISCO America
3 CHICAGO
4 NEWYORKCITY
[...] month was started with answering one of the most common question asked me to about What is Adventureworks? Many of you know the answer but to the surprise more number of the reader did not know the answer. [...]
iam selecting top 10 rank records for the sql table. i want to get the particular rank holder for the table. for example i want to know the third rank holder only. how to write query for this. please help me.