[posted and mailed, please reply in news]
Narine (na***************@prurealty.com) writes:
I need to write one complicated update statement and I'm looking at
maybe finding a simpler way to do it.
I have 2 tables:
1.Photo Table
PhotoID FileName
1 111.jpg
1 111_01.jpg
2 222.jpg
2 222_01.jpg
2 222_02.jpg
3 333.jpg
2.PhotoReport Table
PhotoID FileName1 FileName2 FileName3.....FileName12
1 111.jpg 111_01.jpg NULL NULL
2 222.jpg 222_01.jpg 222_02.jpg
.
.
.
I need to update PhotoReport Table to look like an example above. I've
started writing my code and it looks very hedeous with multiple nested
cursors.
So if someone has a sample of a code to accomplish this, please pretty
please send it my way. I appreciate it in advance.
Cursors? You don't need cursors for this! But you here you get a
12-way self-join. Here I use a temp table, to make this a little
less verbose and more efficient:
CREATE TABLE photos (id int NOT NULL,
filename varchar(23) NOT NULL,
CONSTRAINT pk_photos PRIMARY KEY (id, filename))
go
CREATE TABLE photoreport (id int NOT NULL,
filename1 varchar(23) NOT NULL,
filename2 varchar(23) NULL,
filename3 varchar(23) NULL,
filename4 varchar(23) NULL,
filename5 varchar(23) NULL,
filename6 varchar(23) NULL,
filename7 varchar(23) NULL,
filename8 varchar(23) NULL,
filename9 varchar(23) NULL,
filename10 varchar(23) NULL,
filename11 varchar(23) NULL,
filename12 varchar(23) NULL DEFAULT 'test',
CONSTRAINT pk_report PRIMARY KEY(id))
go
INSERT photos (id, filename) values (1, '111.jpg')
INSERT photos (id, filename) values (1, '111_01.jpg')
INSERT photos (id, filename) values (2, '222.jpg')
INSERT photos (id, filename) values (2, '222_01.jpg')
INSERT photos (id, filename) values (2, '222_02.jpg')
INSERT photos (id, filename) values (3, '333.jpg')
INSERT photos (id, filename) values (3, '333_01.jpg')
INSERT photos (id, filename) values (3, '333_03.jpg')
go
INSERT photoreport (id, filename1)
SELECT id, MIN(filename)
FROM photos
GROUP BY id
go
SELECT id, filename,
rowno = (SELECT COUNT(*)
FROM photos p2
WHERE p1.id = p2.id
AND p1.filename >= p2.filename)
INTO #temp
FROM photos p1
ORDER BY id, filename
UPDATE photoreport
SET filename1 = t1.filename,
filename2 = t2.filename,
filename3 = t3.filename,
filename4 = t4.filename,
filename5 = t5.filename,
filename6 = t6.filename,
filename7 = t7.filename,
filename8 = t8.filename,
filename9 = t9.filename,
filename10 = t10.filename,
filename11 = t11.filename,
filename12 = t12.filename
FROM photoreport r
JOIN #temp t1 ON t1.id = r.id
AND t1.rowno = 1
LEFT JOIN #temp t2 ON t2.id = r.id
AND t2.rowno = 2
LEFT JOIN #temp t3 ON t3.id = r.id
AND t3.rowno = 3
LEFT JOIN #temp t4 ON t4.id = r.id
AND t4.rowno = 4
LEFT JOIN #temp t5 ON t5.id = r.id
AND t5.rowno = 5
LEFT JOIN #temp t6 ON t6.id = r.id
AND t6.rowno = 6
LEFT JOIN #temp t7 ON t7.id = r.id
AND t7.rowno = 7
LEFT JOIN #temp t8 ON t8.id = r.id
AND t8.rowno = 8
LEFT JOIN #temp t9 ON t9.id = r.id
AND t9.rowno = 9
LEFT JOIN #temp t10 ON t10.id = r.id
AND t10.rowno = 10
LEFT JOIN #temp t11 ON t11.id = r.id
AND t11.rowno = 11
LEFT JOIN #temp t12 ON t12.id = r.id
AND t12.rowno = 122
SELECT * FROM photoreport
go
DROP TABLE #temp, photoreport, photos
go
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
