By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,994 Members | 1,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,994 IT Pros & Developers. It's quick & easy.

Complicated Update Statement

P: n/a
Hi All,

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.

Thank you,
Narine
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Narine" <na***************@prurealty.com> wrote in message
news:56**************************@posting.google.c om...
Hi All,

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.

Thank you,
Narine


You're looking for a crosstab query:

http://www.aspfaq.com/show.asp?id=2462

Simon
Jul 20 '05 #2

P: n/a
[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
Jul 20 '05 #3

P: n/a


Simon and Erland,

Thank you very much for helping me out here.
Erland's example worked for me because in my case I had to generate a
rownum column to be able to use the Case statement.

Thanks a million,
Narine

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.