SQL Server: How Do I Convert a Single Column w/Multiple Rows into a Single Row w/Multipe Columns

Please see the attached image for what I am trying to accomplish (needs to be dynamic, as the number of rows are going to be variable).
Need to take values in the first result and display them as you seem them in the 2nd result.
Let me know if you need any more info.

Thank you!
infotechelgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
This would be easier if you posted the code you are using to generate that resultset.

Here is a link to instructions on how to write a dynamic pivot query.

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
0
arnoldCommented:
You are looking at a table pivot.
0
Kevin CrossChief Technology OfficerCommented:
I understand you have a variable number of rows; however, if there are boundaries, you could consider doing this without dynamic SQL.  For example, if you only ever get 25 images max, you always pivot 25 knowing the latter columns may be NULL.

In either case, I think you will need to use ROW_NUMBER() In the solution.  Based on the sample, an ORDER BY the image name itself seems sufficient.

/* this part is for sample data. */
;WITH your_table(IMAGES) AS (
	SELECT CONVERT(VARCHAR(255), '16_app.jpg')
	UNION SELECT CONVERT(VARCHAR(255), '18_app.jpg')
	UNION SELECT CONVERT(VARCHAR(255), '17_app.jpg')
)
/* example starts here. */
SELECT [1] AS IMAGE1
     , [2] AS IMAGE2
     , [3] AS IMAGE3
     , [4] AS IMAGE4
     , [5] AS IMAGE5
FROM (
	SELECT IMAGES
		 , RN = ROW_NUMBER() OVER(ORDER BY IMAGES)
	FROM your_table
) dta
PIVOT (
	MAX(IMAGES)
	FOR RN
	IN ([1],[2],[3],[4],[5])
) pvt
;

Open in new window


If you can get away with this approach, just add additional numbers to support your max number of images.  If not, see above Experts recommendation on how to do the dynamic part.  I would take advantage of a numbers table and use similar query, though, then just make it dynamic.

For example, you can find the COUNT of images, then select that many rows from your numbers table to fill two variables.  One with the number (n) in brackets (e.g., [n]), and other with the number formatted for final SELECT statement (e.g., COALESCE([n], '') AS IMAGEn, noting you can add COALESCE or ISNULL when appropriate to your needs).

Here are some additional references:
Dynamic Pivot Procedure for SQL Server - http://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
Pivot Rolling Periods Without Using Dynamic T-SQL - http://www.experts-exchange.com/articles/654/Pivot-Rolling-Periods-Without-Using-Dynamic-T-SQL.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

infotechelgAuthor Commented:
Thanks, everyone. Brian, I've never used pivot tables before, but every example I've seen seems like it requires using an aggregation function, and those (SUM(), MAX(), etc) require some sort of integer. My needs do not have any kind of values to aggregate; they're all strings.

This is the query that pulls the first results set in my original post:

SELECT DISTINCT FILE_NAME AS IMAGES FROM elg.tbl_GALLERY_FILE g
INNER JOIN elg.xref_GALLERY x ON x.GALLERY_FILE_ID = g.GALLERY_FILE_ID WHERE x.PRODUCT_NAME_ID = 1 AND g.DOWNLOADABLE = 1

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
That's why Brian's example uses MAX.  It works with string values.  Moreover, because you are pivoting a single string value per row, it doesn't matter if you pick the MIN or MAX as there only is one string.  Therefore, the solution will work even with strings.

See my example above how to build ROW_NUMBER for use as the pivot column.  It ensures that you have just one image name to pivot as you will get a unique row number even if you have same file names some how.
1
infotechelgAuthor Commented:
I did not realize MAX worked with strings! Ok, i'll give that a shot. Thanks :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.