Solved

SQL Results horizontal

Posted on 2011-09-26
2
337 Views
Last Modified: 2012-05-12
I found this sample online:

DECLARE @id_list VARCHAR(MAX) --varchar max works for SQL 2005 or above
 
SELECT @id_list = CASE WHEN @id_list IS NULL THEN CONVERT(VARCHAR,column1)
            ELSE @id_list + ',' + CONVERT(VARCHAR,column1) END
            FROM table1            
SELECT @id_list



which works great to display a single column horizontally but I need to display one column normally with the results of another horizontally:

Bob smith  |  09/12/2011, 09/13/2011, 09/15/2011    

in that example all dates with the name value bob smith.
0
Comment
Question by:RustyZ32
2 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
Comment Utility
Hi,

You could use FOR XML


SELECT Name, STUFF((SELECT ', '+CONVERT(varchar, t2.Date, 101) AS[text()] FROM table1 t2 WHERE t2.Name=t1.Name FOR XML PATH('')), 1, 2, '') AS list
FROM table1 t1
GROUP BY t1.Name


/peter
0
 
LVL 6

Author Closing Comment

by:RustyZ32
Comment Utility
worked great! thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now