CODEWITHSUNDEEP
sqlsql-serverdatabase
James
James

Reputation: 2246

sql query for the below pattern

I have a table : Name

When I do a : SELECT * FROM Name, it give the results as :

Name
======
aaa
bbb
ccc
sss

I want the result to be like in one row only :

Name
====
aaa bbb ccc sss

How can I get this?

Upvotes: 1

Views: 106

Answers (3)

Confused Badger
Confused Badger

Reputation: 21

May be over-egging the pudding for your requirement but a CLR aggregate function for concatenating rows into single values is a very handy thing to have in your toolbox. Something like below. Runs much faster than xml in my experience, and supports GROUP BY etc so is quite powerful. Only works in SQL2k8 or above and will blow up if it concats a string to over 2GB.

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1)]
public struct Concatenate : IBinarySerialize
{
public string concat;
public string delimiter;

public void Init()
{
    this.concat = "";
}

public void Accumulate(SqlString text, SqlString delim)
{
    if (!text.IsNull)
    {
        concat += (string)text + (string)delim;
    }
    delimiter = (string)delim;
}

public void Merge(Concatenate Group)
{
    concat += (string)Group.concat + (string)Group.delimiter;
    delimiter = (string)Group.delimiter;
}

public SqlString Terminate()
{
    return concat.Substring(0, concat.Length - delimiter.Length);
}

public void Read(BinaryReader r)
{
    delimiter = r.ReadString();
    concat = r.ReadString();
}

public void Write(BinaryWriter w)
{
    w.Write(delimiter);
    w.Write(concat);
}

}

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

SELECT 
    (SELECT ColumnName + ' '
    FROM YourTable
    FOR XML PATH(''),type).value('.','nvarchar(max)')  AS [Name]

Upvotes: 2

Devart
Devart

Reputation: 121922

Try this one -

DECLARE @temp TABLE
(
      col NVARCHAR(50)
)

INSERT INTO @temp (col)
VALUES 
    ('aaa'),
    ('bbb'),
    ('ccc'),
    ('sss')

SELECT str_string = (
    SELECT col + ' '
    FROM @temp
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

Or try this -

DECLARE @string NVARCHAR(MAX) = ''

SELECT @string = @string + col + ' '
FROM @temp

SELECT @string

Upvotes: 3

Related Questions