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 thi开发者_开发百科s 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).

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Pivot table for Microsoft SQL Server

This isn't possible without using dynamic SQL. PIVOT requires you to specify the columns still.

Let me know if dynamic SQL is acceptable and I'll spin you an example.

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