Reputation: 79

display rows as column

I want to display rows as column in SQL Server.

My table looks like this:

images_id  item_id  images_name
-------------------------------
1          1        image1.jpg 
2          1        image2.jpg 
3          1        image3.jpg 
4          2        image4.jpg 
5          2        image5.jpg 
6          2        image6.jpg

I'd like this output:

images_id  item_id  image1      image2      image3    
------------------------------------------------------
1          1        image1.jpg  image2.jpg  image3.jpg
2          2        image4.jpg  image5.jpg  image6.jpg

Here is an image link.

Is this possible or not? item_id must be dynamically changeable (it is not stable).

Upvotes: 2

Answers (3)

Taryn

Reputation: 247870

Here is how you can use Dynamic SQL for this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME('image' + cast(row_number() over(partition by itemid order by imageid) as varchar(5))) 
            FROM test c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT itemid, ' + @cols + ' from 
            (
                select itemid, imagename, 
                  ''image'' + cast(row_number() over(partition by itemid order by imageid) as varchar(5)) col
                from test
           ) x
            pivot 
            (
                min(imagename)
                for col in (' + @cols + ')
            ) p '


execute(@query)

See SQL Fiddle with Demo

Upvotes: 0