Sql Server - Is there any way to `Concat` nvarchar column in Select Such as `Agregate functions`

Question

Hi I Have Table that called Tags, in tag table I have 2 columns (QuestionID int ,Tag nvachar(100))
I want to Select Questions with all Tags in one column like the below

QuestionID  Tag
----------  ----
1   Math
1   Integral
2   Physics


QuestionID  QuestionText
----------  -----------
1       What is 2*2?
2       What is Quantom roles?

QuestionID  QuestionText        Tags
----------  -----------     -------
1       What is 2*2?        Math, Integral
2       What is Quantom roles?  Physics

Can any one help me with out using scalar value function

http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 or http://stackoverflow.com/questions/941103/concat-groups-in-sql-server or http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — , Oct 10 '14 at 11:12

score 1 · Answer 1 · answered Oct 10 '14 at 11:04

CREATE TABLE #temp
  (
     QuestionID INT,
     Tag        NVARCHAR(100)
  )

INSERT INTO #temp
            (QuestionID,Tag)
VALUES     (1,N'Math'),
            (1,N'Integral'),
            (2,N'Physics')

CREATE TABLE #temp1
  (
     QuestionID   INT,
     QuestionText NVARCHAR(100)
  )

INSERT INTO #temp1
            (QuestionID,QuestionText)
VALUES     (1,N'What is 2*2?'),
            (2,'What is Quantom roles?')

SELECT h.QuestionID,
       h.QuestionText,
       Stuff((SELECT ', ' + CONVERT(VARCHAR, b.TAG)
              FROM   #temp b
              WHERE  b.QuestionID = h.QuestionID
              FOR XML PATH('')), 1, 2, '')
FROM   #temp t
       JOIN #temp1 h
         ON t.QuestionID = h.QuestionID
GROUP  BY h.QuestionID,
          h.QuestionText

score 1 · Answer 2 · answered Oct 10 '14 at 11:05

SELECT q.QuestionText
    ,STUFF((
            SELECT ', ' + t2.Tag
            FROM Tags t2
            WHERE t1.QuestionID = t2.QuestionID
            ORDER BY t2.Tag
            FOR XML PATH('')
                ,TYPE
            ).value('.', 'varchar(max)'), 1, 2, '') AS Tag
FROM Questions q
INNER JOIN Tags t1
    ON q.QuestionID = t1.QuestionID
GROUP BY q.QuestionText
    ,t1.QuestionID

Working example : http://sqlfiddle.com/#!3/e8f0f/7

Upendra Chaudhari · Answer 3 · 2014-10-10T12:56:50.773

1

Try sub query to concat column data in comma separated values like below :

SELECT  [QuestionID],
        [QuestionText],
        STUFF(( SELECT  ',' + [Tag]
        FROM    [dbo].[Tags]
        WHERE   [QuestionID] = [Question].[QuestionID]
        FOR XML PATH ('')), 1, 1, '') AS [Tags]
FROM [dbo].[Question]

SQL Fiddle Demo

edited Oct 10 '14 at 12:56

answered Oct 10 '14 at 11:05

Upendra Chaudhari

6,473
5
25
42

`STUFF` doesn't perform any string concatenation. `STUFF` is being used to remove a `,` at the start of the string after concatenation has already occurred. – Damien_The_Unbeliever Oct 10 '14 at 12:18
Oh, sorry, I have changed my answer – Upendra Chaudhari Oct 10 '14 at 12:57

score 1 · Answer 4 · answered Oct 10 '14 at 11:11

Try this

create function fn_comma (@question_id int)
returns varchar(100)
as
begin
declare @value varchar(100)
set @value=(SELECT  top 1 STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM Tags
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM Tags
--where id=1
GROUP BY ID)
return @value
end

score 1 · Accepted Answer · edited Oct 14 '14 at 06:02

There are two ways to answer this:

can use a query like in other answer, but this is work for one table only.
create clr aggregate function for this like a below code (my code in C#).
this solution work for all tables and simple for use,
only use: select Concat(column) from Table in sql server

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;


[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = false, IsNullIfEmpty = true, MaxByteSize = -1)]
public struct Concat : IBinarySerialize
{

    public void Init()
    {
        SB = new StringBuilder();
    }

    public void Accumulate(SqlString Value)
    {
        if (Value.IsNull)
            return;
        if (SB.Length > 0)
            SB.Append("\n");
        SB.Append(Value);
    }

    public void Merge(Concat Group)
    {
        if (SB.Length > 0 && Group.SB.Length > 0)
            SB.Append("\n");
        SB.Append(Group.SB.ToString());
    }

    public SqlString Terminate()
    {
        return new SqlString(SB.ToString());
    }

    // This is a place-holder member field
    StringBuilder SB;

    public void Read(System.IO.BinaryReader r)
    {
        SB = new StringBuilder(r.ReadString());
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(SB.ToString());
    }

}

score 0 · Answer 6 · edited Nov 27 '14 at 09:03

Try the below idea. You just need to rewrite it as a function, then it will return all tags for the question id:

declare @function_in_questionid_para as @int

with std as 
(select *,ROW_NUMBER() over(partition by QuestionID order by QuestionID,tag) as dd from #temp)
 select * #temp3 into from std
 declare @counter as int
 set @counter = (select count(*) from #temp where QuestionID = @function_in_questionid_para as @int)
 declare @c as int = 1

 declare @tags as varchar(200) = ''
 while (@c <= @counter)
 begin
 if (@c > 1)  set @tags = @tags + ', ' 
 set @tags = @tags + (select tag from #temp3 where QuestionID = @function_in_questionid_para as @int and dd = @c)
 set @c = @c + 1
 end
 print @tags

Hello and welcome. Can you reformat your answer and fix any spelling mistakes? (I'm not familiar with the language in questions, but I'm willing to bet 'decalre' isn't a statement :) ) — GHC, Nov 27 '14 at 08:30

Sql Server - Is there any way to `Concat` nvarchar column in Select Such as `Agregate functions`

6 Answers6

Linked