Transposition from column to row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Transposition from column to row

Author

Topic

Shamee_1321
Starting Member

15 Posts

Posted - 2008-02-04 : 01:08:52

I have a table 'TestTranspo' with 1 column named 'Col1' (varchar(50)).
The data entered for Col1 are aaa, bbb, ccc, ddd. Normally when, I do a select statement the result will be like this:

aaa
bbb
ccc
ddd

What I should do is to give the results in one row:

aaa bbb ccc ddd

The following query works but my senior told me to use a stored procedure and pass the tablename, the column and the delimiter as parameter to get the answer..

declare @Str varchar(100)

Select @Str = COALESCE(@Str + ',','') + Col1 FROM TestTranspo

select @Str

What should I do???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 01:41:03

Try this:-

ALTER PROC GetRow
@Table varchar(20),
@Col varchar(20),
@delimiter char(1)
AS
DECLARE @Sql varchar(8000)
SET @Sql='SELECT LEFT(l.list,LEN(l.list)-1) FROM (SELECT CAST(' + @Col + ' AS varchar(10)) + ''' + @delimiter + ''' AS [text()] FROM ' + @Table + ' FOR XML PATH(''''))l(list)'
EXEC (@Sql)
GO