ROWS TO SINGLE CELL

Question!

I would like to get the desired output marked in green

enter image description here

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



Answers

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

concatenate_col

By : AT-2016


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


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


This video can help you solving your question :)
By: admin