| Author |
 Topic  |
|
|
sp_wiz
Yak Posting Veteran
United Kingdom
55 Posts |
 Posted - 10/21/2001 : 13:04:13
|
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
SQLTeam MVY/MIA
USA
12325 Posts |
Posted - 10/21/2001 : 14:10:26
|
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 |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 10/21/2001 : 19:31:45
|
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 |
|
|
|
sp_wiz
Yak Posting Veteran
United Kingdom
55 Posts |
Posted - 10/21/2001 : 19:38:30
|
Cheers for the answer.
I have to get this working for Monday so I would appreciate it if Merkin would post his solution.
Robp
|
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 10/21/2001 : 19:46:19
|
Ummm, I just did
Damian |
|
|
|
davidpardoe
Constraint Violating Yak Guru
United Kingdom
324 Posts |
Posted - 10/22/2001 : 04:01:38
|
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 |
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 10/31/2001 : 20:11:12
|
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"
|
|
|
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
Posted - 10/31/2001 : 23:28:33
|
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"
|
|
|
|
urobertson
Starting Member
9 Posts |
Posted - 10/30/2003 : 14:52:13
|
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 |
|
|
| |
Topic |
|
|
|
Cursor or Not
