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 2008 Forums
 Transact-SQL (2008)
 combining multiple records into one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MikeSaunders
Starting Member

11 Posts

Posted - 06/18/2013 :  06:23:20  Show Profile  Reply with Quote
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 pictures

DROP TABLE pictures

Of 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 5
1, picture1, picture2, picture3, picture4, picture5

I know this is possible, but unfortunately still struggling.

Can someone help me please

Thanks a lot!

Mike

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/18/2013 :  06:34:37  Show Profile  Visit ditch's Homepage  Reply with Quote
something like this:

select
p1.userid,
p1.picture1,
p2.picture2,
p3.picture3,
p4.picture4,
p5.picture5
from
(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 p4
on p1.userid = p4.userid
inner join
(SELECT userid, picture5 FROM pictures ) as p5
on p1.userid = p5.userid


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  06:37:09  Show Profile  Reply with Quote

SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY picture) AS Seq,* FROM pictures) p
PIVOT (MAX(picture) FOR Seq IN ([1],[2],[3],[4],[5]))t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  06:40:17  Show Profile  Reply with Quote
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.05 seconds. Powered By: Snitz Forums 2000