Results to row instead of column

Dan Crowell

Is there a Transact SQL statement I can use to get my results in a row instead of a column I thought of PIVOT but couldn't get it to work. I think I have done this before but I cannot remember to keyword to use.

For example:

SELECT TOP 3 [FirstName] + ','
FROM [AdventureWorks].[Person].[Contact]

This query returns:

Gustavo,
Catherine,
Kim,

I would like it to return:

Gustavo,Catherine, Kim,

Thanks in advance.

Arnie Rowland

There is not a 'simple' build in way to accomplish your task (it is normally left to the client application to 'arrange' data for display.

However, here are several 'work-arounds' that may help you.

Lists -Field Concatenation
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

Lists -Field Concatenation ( For SQL 2000 & 2005 )
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

Lists -Recursive Queries
http://www.paragoncorporation.com/ArticleDetail.aspx ArticleID=9
http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsqlpro03/html/sp03i8.asp
http://www.wwwcoder.com/main/parentid/191/site/1857/68/default.aspx
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp

Kent Waldrop 2007 Mar

You should be able to correlate a select FOR XML to accomplish what you need -- something like the idea of:

declare @nameSample table
   (    firstName   varchar(13)
   )
insert into @nameSample values ('Gustavo')
insert into @nameSample values ('Catherine')
insert into @nameSample values ('Kim')

select firstName + ',' as [data()]
from @nameSample
order by firstName
for xml path ('')

--   XML_...
--   ------------------------
--   Catherine, Gustavo, Kim,

I didn't realize that Arnie had already posted a good response to the question. Please excuse me.

Sami Samir

Well, here is a solution although not a really very user firendly one. The idea is first to create a query that contains the row ID. If you are using SQL Server 2005 then you can use the Row_Number() function. If not, then the only way I could think of was to create a temp table with an identity field. After that you need to join the same result set together but with incrementing IDs. Meaning join ID 1 in the first result set with ID 2 in the second one and then join this result set with ID 3 in the 3rd one. Told you it was not a very user friendly answer :)

For SQL Server 2005 the query will be:

Select Top 1 A.[FirstName] + ', ' + B.[FirstName] + ', ' + C.[FirstName]
From (Select Row_Number() Over (order by [FirstName]) as RowID, [FirstName] From [AdventureWorks].[Person].[Contact]) A
Inner Join (Select Row_Number() Over (order by [FirstName]) as RowID, [FirstName] From [AdventureWorks].[Person].[Contact]) B on (A.RowID + 1) = B.RowID
Inner Join (Select Row_Number() Over (order by [FirstName]) as RowID, [FirstName] From [AdventureWorks].[Person].[Contact]) C on (B.RowID + 1) = C.RowID

For SQL Server 2000 it will be:

Create Table #Tmp
(RowID int identity(1,1),
FirstName varchar(256))

Insert Into #Tmp (FirstName)
Select [FirstName] From [AdventureWorks].[Person].[Contact]
Order By [FirstName]

Select Top 1 A.[FirstName] + ', ' + B.[FirstName] + ', ' + C.[FirstName]
From #Tmp A Inner Join #Tmp B on (A.RowID + 1) = B.RowID
Inner Join #Tmp C on (B.RowID + 1) = C.RowID

Drop Table #Tmp

I hope this helps.

Best regards,
Sami Samir

Arnie Rowland

Kent,

Using xml is a very nice solution, but is there an 'easy' way to eliminate the trailing comma

Kent Waldrop 2007 Mar

Arnie:

Thank you for the good question. You are right, it is not as simple as I initially implied -- there is some legwork that you must do. One way to do the legwork is to use the REPLACE function:

declare @nameSample table
( rid integer,
Team integer,
Seq integer,
member varchar(13)
)
insert into @nameSample values (1, 1, 1, 'Mary Ellen')
insert into @nameSample values (2, 1, 2, 'Jack')
insert into @nameSample values (3, 2, 1, 'Gustavo')
insert into @nameSample values (4, 2, 2, 'Catherine')
insert into @nameSample values (5, 2, 3, 'Kim')
insert into @nameSample values (6, 2, 4, 'Bev')
insert into @nameSample values (7, 3, 1, 'Barney')
insert into @nameSample values (8, 3, 2, 'Fred')
insert into @nameSample values (9, 3, 3, 'Betty')
insert into @nameSample values (10,3, 4, 'Dino')

select distinct
team,
replace(replace(
( select replace (member, ' ', '~') as [data()]
from @nameSample x
where seq <= 3
and a.team = x.team
order by Seq
for xml path ('')
), ' ', ','), '~', ' ')
as nameList
from @nameSample a

-- team nameList
-- ----------- ---------------------
-- 1 Mary Ellen,Jack
-- 2 Gustavo,Catherine,Kim
-- 3 Barney,Fred,Betty

Umachandar Jayachandran - MS

You can use the method below:

SELECT pt.[1] + coalesce(', ' + pt.[2], '') + coalesce(', ' + pt.[3], '') as firstnames
FROM (
SELECT TOP 3 [FirstName], ROW_NUMBER() OVER(ORDER BY ContactID) as seq
FROM [AdventureWorks].[Person].[Contact]
ORDER BY [ContactID]
) AS t
PIVOT (min(t.FirstName) for t.seq in ([1], [2], [3])) as pt

This is the fastest method (several times better than xml approach or others) for small number of expressions. The issue is the increased compilation costs as the number of elements in the IN list of the PIVOT clause and expression in SELECT list. Otherwise, you can use this method fine for simple queries where you know the number of values you are going to concatenate.

The xml method or CLR aggregate method (which cannot do ordered concatenation) has fixed overhead. If you have large number of rows to concatenate then they will perform better otherwise the overhead is too much for simple queries.

Results to row instead of column

Re: Results to row instead of column

Re: Results to row instead of column

Re: Results to row instead of column

Re: Results to row instead of column

Re: Results to row instead of column

Re: Results to row instead of column