combining multiple rows into one
-
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
-
WITH CTE AS (SELECT userid,picture,
ROW_NUMBER() OVER(PARTITION BY userid ORDER BY picture) AS rn
FROM pictures)
SELECT userid,
MAX(CASE WHEN rn=1 THEN picture END) AS pic1,
MAX(CASE WHEN rn=2 THEN picture END) AS pic2,
MAX(CASE WHEN rn=3 THEN picture END) AS pic3,
MAX(CASE WHEN rn=4 THEN picture END) AS pic4,
MAX(CASE WHEN rn=5 THEN picture END) AS pic5
FROM CTE
GROUP BY userid
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi SSCommitted,
Super...thanks a lot!
Mike
-
This can also be solved with the PIVOT function:
if OBJECT_ID('tempdb..#temp') is not nulldrop table #temp
create table #temp (userid int, picture varchar(50))
insert into #temp values(1, 'picture1.gif')
insert into #temp values(1, 'picture2.gif')
insert into #temp values(1, 'picture3.gif')
insert into #temp values(1, 'picture4.gif')
insert into #temp values(1, 'picture5.gif')
insert into #temp values(2, 'picture6.gif')
insert into #temp values(2, 'picture7.gif')
insert into #temp values(2, 'picture8.gif')
insert into #temp values(2, 'picture9.gif')
insert into #temp values(2, 'picture0.gif')
;with cte_rownr as
(
select
row_number() over (partition by userid order by picture) as rownr
, userid
, picture
from #temp
)
select *
from cte_rownr
PIVOT
(max(picture)
for rownr in ([1], [2], [3], [4], [5])
) as pvt
if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply