concatinating two column records to get the aggregated third column

Question

I have two columns, column1 and 2 with 10 records each for example:

Column1 (A, A, A, B, B, B, B, C, C, D) and Column2 (1, 1, 2, 3, 3, 2, 1, 4, 4, 1)

I need to create third column, column3 with these two columns such that, column3= (A_1_1_2, B_3_3_2_1, C_4_4, D_1)

Please help me how to do this, I am using group by statements and also concatinations but not able to figure out how.

Answer 1

Try this:

DECLARE @T TABLE([col1] VARCHAR(1), [col2] VARCHAR(1));

INSERT INTO @T ([col1], [col2])
VALUES 
('A','1'), ('A','1'), ('A','2'), 
('B','3'), ('B','3'), ('B','2'), 
('B','1'), ('C','4'), ('C','4'), ('D','1');

DECLARE @col1 VARCHAR(1), @col2 VARCHAR(1), @CONCAT VARCHAR(MAX);

DECLARE col1_cursor CURSOR
FOR
SELECT DISTINCT col1
FROM @T;

OPEN col1_cursor;

FETCH NEXT
FROM col1_cursor
INTO @col1;


WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CONCAT = ''

    DECLARE col2_cursor CURSOR
    FOR
    SELECT col2
    FROM @T
    WHERE col1 = @col1;

    OPEN col2_cursor;

    FETCH NEXT
    FROM col2_cursor
    INTO @col2;


    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF (@CONCAT = '')
        BEGIN
            SET @CONCAT = @col1 + '-' + @col2;
        END
        ELSE
        BEGIN
            SET @CONCAT = @CONCAT + '-' + @col2
        END


        FETCH NEXT
        FROM col2_cursor
        INTO @col2;
    END

    PRINT @CONCAT

    CLOSE col2_cursor;

    DEALLOCATE col2_cursor;


    FETCH NEXT
    FROM col1_cursor
    INTO @col1;

END

CLOSE col1_cursor;

DEALLOCATE col1_cursor;
GO

Answer 2

I used the work found in this previous question for most of my answer.

CREATE TABLE MyTable (COLUMN1 char(1) NULL, COLUMN2 char(1) NULL);

INSERT INTO MyTable VALUES ('A', '1'), ('A', '1'), 
('A', '2'), ('B', '3'), ('B', '3'), ('B', '2'), 
('B', '1'), ('C', '4'), ('C', '4'), ('D', '1');

SELECT COLUMN1 + COLUMN2 AS COLUMN3
FROM(
  SELECT DISTINCT a.COLUMN1,
    (SELECT '_' + b.COLUMN2
     FROM MyTable b
     WHERE a.COLUMN1 = b.COLUMN1
     ORDER BY b.COLUMN1
     FOR XML PATH ('')) AS COLUMN2
  FROM MyTable a
  )c

demo

Hopefully this should get you to a point that you can modify the query to serve your needs.

As a plus, this method is able to avoid the dreaded cursor.

Answer 3

Here you go. You need to retain the 3rd column and only output on the last observation of each Col1 group:

data test;
input Col1 $ Col2;
datalines;
A 1
A 1
A 2
B 3
B 3
B 2
B 1
C 4
C 4
D 1
;;
run;

data test_out;
set test;
by Col1;
format Col3 $64.;
retain Col3;
if first.Col1 then
    Col3 = strip(Col1);

Col3 = catx("_",col3,col2);

if last.Col1 then
    output;
run;