Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
MikeSaunders
Starting Member
11 Posts |
Posted - 2013-06-18 : 06:23:20
|
Hello all,I have a table that stores pictures for my users but now i need to get multiple pictures into one row.Hereunder you'll find my start code:CREATE TABLE pictures(userid int, picture varchar(100))INSERT INTO pictures (userid, picture) VALUES (1, 'picture1.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture2.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture3.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture4.gif')INSERT INTO pictures (userid, picture) VALUES (1, 'picture5.gif')SELECT userid, picture FROM picturesDROP TABLE picturesOf course it is simple to get five records (always five) with pictures as mentioned above, but now i need a result that shows me one record like this:userid, pic1, pic2, pic3, pic4, pic 51, picture1, picture2, picture3, picture4, picture5I know this is possible, but unfortunately still struggling.Can someone help me pleaseThanks a lot!Mike |
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2013-06-18 : 06:34:37
|
something like this:select p1.userid, p1.picture1, p2.picture2, p3.picture3, p4.picture4, p5.picture5from (SELECT userid, picture1 FROM pictures ) as p1 inner join(SELECT userid, picture2 FROM pictures ) as p2 on p2.userid = p1.userid inner join (SELECT userid, picture3 FROM pictures ) as p3 on p1.userid = p3.userid inner join (SELECT userid, picture4 FROM pictures ) as p4on p1.userid = p4.userid inner join (SELECT userid, picture5 FROM pictures ) as p5on p1.userid = p5.userid Duane.http://ditchiecubeblog.wordpress.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 06:37:09
|
[code]SELECT *FROM (SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY picture) AS Seq,* FROM pictures) pPIVOT (MAX(picture) FOR Seq IN ([1],[2],[3],[4],[5]))t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 06:40:17
|
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|