Rows to single cell

Question

I would like to get the desired output marked in green

the data points for each id get put into a single cell

Basically take all the events that have happened with A and attach it in the same order

Also add more information such as table names, and what have you tried? And how do we know what order the items in the event column should be displayed in? It looks alphabetical, but is it? — BeanFrog, Sep 22 '16 at 09:31
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Sep 22 '16 at 09:39
But why do you want to do that?!? Seems like a pretty bad idea to me. — jarlh, Sep 22 '16 at 09:41
Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — qxg, Sep 22 '16 at 09:41
i'm doing it to see which id have a common sequence to prove that the event flow is random — Ryan Gomes, Sep 22 '16 at 10:34

score 1 · Answer 1 · answered Sep 22 '16 at 09:36

1

You can use FOR XML:

SELECT DISTINCT 
            ID,
            (SELECT [EVENT] +''
            FROM YourTable
            WHERE ID = y.ID
            FOR XML PATH('')
            ) as [EVENT]
FROM YourTable y

Output:

ID  EVENT
1   AABCD
2   AABBCC

answered Sep 22 '16 at 09:36

gofr1

15,741
11
42
52

I don't understand why you haven't tried my solution? it is simple and clear, the one you choose as answer is a copy of my answer with STUFF (which has no use here). The other one that uses temp table and is copy of my answer is upvoted... – gofr1 Sep 22 '16 at 12:51

score 1 · Accepted Answer · answered Sep 22 '16 at 09:40

1

Use Stuff Function:

DECLARE @tblTest AS Table(
    ID INT,
    EVENT VARCHAR(5)
)

INSERT INTO @tblTest VALUES
(1,'A'),
(1,'A'),
(1,'C'),
(2,'A'),
(2,'B'),
(2,'C')

SELECT DISTINCT
    T1.ID,
    STUFF
    (
         (SELECT '' + convert(varchar(10), T2.EVENT, 120)
          FROM @tblTest T2
          where T1.ID = T2.ID
          FOR XML PATH (''))
    , 1, 0, '')  AS EVENT
FROM @tblTest T1

answered Sep 22 '16 at 09:40

Sandip - Frontend Developer

14,397
4
35
61

, 1, 0, '') the parentheses are missing – Ryan Gomes Sep 22 '16 at 10:36
i have answer after test in my system, no any such error – Sandip - Frontend Developer Sep 22 '16 at 10:37
I know what it is :) There is no need to use it here. It is often used when you need to separate values coming `FOR XML PATH ('')`, but here it has no use - no separators. – gofr1 Sep 22 '16 at 11:57

score 1 · Answer 3 · answered Sep 22 '16 at 09:55

You can use UDF to do so as follows:

CREATE TABLE t(
id INT,
col CHAR(1)
);

INSERT INTO t VALUES (1,'a');
INSERT INTO t VALUES (1,'b');
INSERT INTO t VALUES (1,'c');
INSERT INTO t VALUES (1,'d');
INSERT INTO t VALUES (2,'e');
INSERT INTO t VALUES (2,'f');
INSERT INTO t VALUES (3,'g');
INSERT INTO t VALUES (4,'h');

The UDF (User defined function) -

USE [t]
GO
CREATE FUNCTION dbo.ConcatenateCols(@Id INT)

RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @RtnStr VARCHAR(MAX)

SELECT @RtnStr = COALESCE(@RtnStr + '','') + col
FROM dbo.t
WHERE id = @Id AND col > ''
RETURN @RtnStr

END
GO

Finally the query and result:

SELECT id, dbo.ConcatenateCols(id) AS Cols -- UDF - ConcatenateCols(id)
FROM t GROUP BY Id

score 0 · Answer 4 · edited Sep 22 '16 at 09:42

0

CREATE TABLE #temp(Id INt,Event Nvarchar(25))
INSERT INTO #temp
SELECT 1,
       'A'
UNION ALL
SELECT 1,
       'A'
UNION ALL
SELECT 1,
       'B'
UNION ALL
SELECT 1,
       'C'
UNION ALL
SELECT 1,
       'D'
UNION ALL
SELECT 2,
       'A'
UNION ALL
SELECT 2,
       'A'
UNION ALL
SELECT 2,
       'B'
UNION ALL
SELECT 2,
       'B'
UNION ALL
SELECT 2,
       'C'
UNION ALL
SELECT 2,
       'C'
SELECT DISTINCT ID,

  (SELECT [EVENT] +''
   FROM #temp
   WHERE ID = y.ID
     FOR XML PATH('') ) AS [EVENT]
FROM #temp y

edited Sep 22 '16 at 09:42

jarlh

42,561
8
45
63

answered Sep 22 '16 at 09:40

Alfaiz Ahmed

1,698
1
11
17

Copy of my answer. – gofr1 Sep 22 '16 at 11:49

Rows to single cell

4 Answers4