SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Cursor or Not
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sp_wiz
Yak Posting Veteran

United Kingdom
55 Posts

Posted - 10/21/2001 :  13:04:13  Show Profile  Visit sp_wiz's Homepage  Reply with Quote
I have 2 tables 1 called PHOTOS the Other Keywords.

Each row in photos can have many rows in keywords.

How do I display a recordset with the Photo table details with the related keywords.

I really need to get the keywords into a delimitted string

Example
PhotoID, title , Keywords
1,My photo , family,children,kids
2, Wedding, bridesmaid,nightmare

Does this make sense

I realise if i only wanted to display 1 record for photos this would be fairly simple.

Robp


robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/21/2001 :  14:10:26  Show Profile  Visit robvolk's Homepage  Reply with Quote
NO CURSORS!!!

I know Merkin will scream at me, because I came up with this solution and started an article I never finished (of course, when I asked him to read it, he came up with an EVEN BETTER solution, but of course he hasn't written HIS article on it either, so screw him!)

Anyway, here it is:

DECLARE @rows int
SELECT @rows=1

set nocount on
SELECT PhotoID, CONVERT(varchar(1000),'') AS KeyWords INTO #csvtemp
FROM Photos GROUP BY PhotoID

UPDATE C Set C.CSV=K.KeyWord
FROM #csvtemp C INNER JOIN
(SELECT PhotoID, Min(KeyWord) KeyWord FROM Keywords GROUP BY PhotoID) K
ON (C.PhotoID=K.PhotoID)

WHILE @rows>0
BEGIN
UPDATE C Set C.CSV=C.CSV + ', ' + K.KeyWord
FROM #csvtemp C INNER JOIN KeyWords K ON (C.PhotoID=K.PhotoID)
WHERE CharIndex(K.Keyword), C.CSV)=0
SELECT @rows=@@ROWCOUNT
END

SELECT * FROM #csvtemp
DROP TABLE #csvtemp
--this line is optional but it's a good idea to keep it

This batch creates a temp table with each photoID in it, and an empty column for your CSV keywords. It puts the first (minimum) keyword into the CSV column. Then it loops through the remaining keywords and appends them to the CSV. Finally it SELECTs the results from the temp table, then drops it. It will fit nicely into a stored procedure. You can include the title in the temp table, or just JOIN the temp table to Photos by PhotoID to get it.

While this has a loop, it is NOT a cursor, because A) there is no CURSOR declaration and B) each iteration of the loop handles a SET of photos and keywords, not an individual photo or keyword.

Merkin's solution uses COALESCE() I think, and it was considerably faster than this solution, so if you can hang on for a while I'm sure he'll post it.

Edited by - robvolk on 10/21/2001 14:12:18
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/21/2001 :  19:31:45  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

Ok ok, here is my way. I have been thinking about doing an article on this lately, I still might.

I actually got thinking about this after seeing David Pardoe do something like it in the forum. So credit has to go to him. Anyway, it works by using a temp table with an identity field on it, then looping through that using the identity column as a row number. It is still a loop, but is better than a cursor because it doesn't involve you opening each row....

anyway, here is some code.




--Create your temp table
Create Table #PhotoTemp(
TempId int NOT NULL Identity,
PhotoId int,
Title VarChar(100),
Keywords VarChar(3000)
)

--Populate it with your photos

Insert into #PhotoTemp (PhotoID, Title)
SELECT
PhotoID, Title
FROM
Photos

declare @keywords nvarchar(3000)
declare @iRow int
declare @iMax int
Select @iMax = count(*) from #PhotoTemp
Select @iRow = 1
while @iRow <= @iMax

BEGIN

--Build your csv string of keywords
Select @keywords = null
SELECT @Keywords = @Keywords = Coalesce(@Keywords + ', ', '') +
Keyword FROM Keywords
WHERE
PhotoID = (SELECT PhotoID FROM #PhotoTemp WHERE TempID = @iRow)

--Update the temp table
Update #PhotoTemp SET Keywords = @Keywords
WHERE TempID = @iRow

Select @iRow = @iRow + 1
END

SELECT * FROM #PhotoTemp

DROP Table #PhotoTemp


It is fairly straight forward, if you don't get the COALESCE bit, go look for Garth's article on CSVs.

Hope that helps.




Damian
Go to Top of Page

sp_wiz
Yak Posting Veteran

United Kingdom
55 Posts

Posted - 10/21/2001 :  19:38:30  Show Profile  Visit sp_wiz's Homepage  Reply with Quote
Cheers for the answer.

I have to get this working for Monday so I would appreciate it if Merkin would post his solution.

Robp


Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/21/2001 :  19:46:19  Show Profile  Visit Merkin's Homepage  Reply with Quote
Ummm, I just did



Damian
Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 10/22/2001 :  04:01:38  Show Profile  Reply with Quote
Thanks for the mention - if I remember right I was going the other way round; from a csv string into separate fields.

Another smiley for SQLteam community spirit!

============
The Dabbler!

Edited by - davidpardoe on 10/22/2001 04:04:46
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/31/2001 :  20:11:12  Show Profile  Reply with Quote
Just thought I might throw in a version based on the SQL2K Table variable....

It is based on RobVolk's solution...

--<TSQL>
declare @Table table(PhotoID int not null, KeyWord varchar(1000) not null primary key (PhotoID,KeyWord))
Insert @Table (PhotoID, KeyWord) SELECT PhotoID, Min(KeyWord) KeyWord FROM Keywords GROUP BY PhotoID

while @@RowCount > 0
begin
update T set T.KeyWord = Coalesce(T.Keyword + ', ', '') + K.KeyWord
from @Table T
inner join KeyWords K ON K.PhotoID = T.PhotoID
where CharIndex(K.KeyWord, T.KeyWord) = 0
end
Select * from @Table

--</TSQL>

Good points:
Table variable destroyed outside scope of batch.
It looks cool!

Bad Points:
Heavy Update operation.
Seems to run slower than Merkins solution..

You could also alter Merkins solution to use a table variable..

DavidM
It gets windy at a thousand feet...."Tutorial D"
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/31/2001 :  23:28:33  Show Profile  Reply with Quote
And one more using the table variable...

--<TSQL>

declare @Table table(PhotoID int not null primary key, KeyWord varchar(1000) null)
Insert @Table (PhotoID) SELECT PhotoID FROM Keywords GROUP BY PhotoID
declare @Keywords varchar(1000)

while @@RowCount > 0
begin
Set @Keywords = null
Select @Keywords = @Keywords + Coalesce(Keyword + ', ', '') from KeyWords K
Where PhotoID = (Select min(PhotoID) as PhotoID from @Table where KeyWord is null)
Update T set T.KeyWord = left(@Keywords,len(@Keywords)-1)
from @Table T
where T.PhotoID = (Select min(PhotoID) as PhotoID from @Table where KeyWord is null)
end
Select * from @Table

--</TSQL>

DavidM
It gets windy at a thousand feet...."Tutorial D"
Go to Top of Page

urobertson
Starting Member

9 Posts

Posted - 10/30/2003 :  14:52:13  Show Profile  Reply with Quote
Just to add one thing. I used this code and it works great providing I change one thing...


--Build your csv string of keywords
Select @keywords = null


Should be

--Build your csv string of keywords
Select @keywords = ''


And similar for other examples.
Because you can't concatenate to a null. It stays null. (At least it does in my code, W2K, SS 2000)

Thanks for the great code. I really appreciate it.

Thanks,
Ursula
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000