combining multiple records into one

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

combining multiple records into one

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 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
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.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/

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) p
PIVOT (MAX(picture) FOR Seq IN ([1],[2],[3],[4],[5]))t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources