CODEWITHSUNDEEP
sqlsql-server
Mehdi Haghshenas
Mehdi Haghshenas

Reputation: 2447

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

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

Upvotes: 1

Views: 1927

Answers (6)

Mohammed
Mohammed

Reputation: 313

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

Upvotes: 0

user4132661
user4132661

Reputation:

There are two ways to answer this:

  1. can use a query like in other answer, but this is work for one table only.
  2. 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());
    }

}

Upvotes: 1

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

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

Upvotes: 1

Recursive
Recursive

Reputation: 952

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

Upvotes: 1

jazzytomato
jazzytomato

Reputation: 7239

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

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

Related Questions