Combine rows in a table that are only different in one column

[denny1824]

Hi everyone,

Here is my problem. I have a Table Variable (I could easily turn it into a Temp Table instead) that will sometimes have rows that are identical except for one specific column. The column is of nvarchar. When the rest of the columns for those rows are identical, I want to combine all of the values of that column into a single nvarchar with a delimiter in between each value. Then combine the rows where all of the other columns are the same and that specific column now have the single nvarchar.

Another complexity to this is that if that specific column has a null, then I dont want that row combined with the others.

Example:

DECLARE @Table1 TABLE
(
    ID int,
    Column1 nvarchar(10),
    Column2 nvarchar(10),
    Column3 nvarchar(10),
    ColumnX nvarchar(1000)
)
 
INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', NULL)
INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX1')
INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX2')
INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX3')
INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX4')
 

The Desired Result Is:

ID, Column1, Column2, Column3, ColumnX
 1, 'Value1', 'Value2', 'Value3', NULL
 1, 'Value1', 'Value2', 'Value3', 'ValueX1<br/>ValueX2<br/>ValueX3<br/>ValueX4'
 

Any suggestion on how I should start to tackle this problem would be very helpful. I will be working on this on Monday morning. I am using T-SQL.

Thank You,
Denny

[Delerna]

Yes, it would be nice to be able to say

   SELECT ID,Column1,Coulmn2,Column3,concat(Columnx) as Columnx
   FROM @Table1 
   GROUP BY ID,Column1,Coulmn2,Column3
 

and the database egine would just add the strings togeter with a comma separator. Unfortunately there is no such aggregate as concat().

I have done in the past and I think I resorted to a cursor to achieve it.
I believe to do it with a query instead
you will need to be able to sequence each identical ID,Column1,Coulmn2,Column3 from 1 to however many identical records there are in each particular group.

Then you could say something like

SELECT ID,Column1,Coulmn2,Column3,Max(ColumnX1) + ',' + Max(ColumnX2) + etc etc etc as Columnx 
FROM
(   SELECT ID,Column1,Coulmn2,Column3
                ,case when SeqNo=1 then ColumnX else'' end as ColumnX1
                ,case when SeqNo=2 then ColumnX else'' end as ColumnX2
                , etc etc etc
    FROM
    @Table1
)a 
GROUP BY ID,Column1,Coulmn2,Column3
 

of course the practicalities of doing something like that is dependent on the maximum number of different values in any particular group of ID,Column1,Coulmn2,Column3.


Good luck with that and its no wonder im going bald

[denny1824]

Thank you Delerna.

I am not allowed to use cursors, so I had to use an alternative way to loop. I also had to add an identity to the original table. Here is my result:

DECLARE @Table1 TABLE
(
    RowCounter int identity (1,1),
    ID int,
    Column1 nvarchar(10),
    Column2 nvarchar(10),
    Column3 nvarchar(10),
    ColumnX nvarchar(10)
)
 
INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', NULL)
INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X1')
INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X2')
INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X3')
INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X4')
INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', NULL)
INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X1')
INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X2')
INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X3')
INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X4')
 
DECLARE    @iCounter INT,
    @iLoop INT, 
    @iID INT
 
DECLARE @Result TABLE
(
    ID int,
    Column1 nvarchar(10),
    Column2 nvarchar(10),
    Column3 nvarchar(10),
    ColumnX nvarchar(1000)
)
 
SET @iCounter = 1
SELECT @iLoop = Max(RowCounter) FROM @Table1
 
WHILE @iCounter <= @iLoop
BEGIN
SELECT @iID = ID FROM @Table1 WHERE RowCounter = @iCounter
IF @iID IS NOT NULL
IF (NOT EXISTS (SELECT * FROM @Result WHERE ID = @iID AND ColumnX IS NOT NULL) --This ID is not yet in result table
    OR EXISTS(SELECT * FROM @Table1 WHERE RowCounter = @iCounter AND ColumnX IS NULL) --OR the ColumnX is null
)
BEGIN
    INSERT INTO @Result SELECT ID,Column1,Column2,Column3,ColumnX 
        FROM @Table1 WHERE RowCounter = @iCounter --Add Row to result table
    DELETE @Table1 WHERE RowCounter = @iCounter --Remove row from initial table
END
ELSE --A row with ID = @iID and a non-null ColumnX already exists in result table 
BEGIN
    UPDATE @Result SET ColumnX = ColumnX + '<br/>' + (SELECT ColumnX FROM @Table1 WHERE RowCounter = @iCounter)
    FROM @Result WHERE ID = @iID AND ColumnX IS NOT NULL --Update ColumnX
    DELETE @Table1 WHERE RowCounter = @iCounter --Remove row from initial table
END
 
SET @iCounter = @iCounter + 1
END
 
SELECT * FROM @Result

The Desired Resulting Table Is:

ID, Column1, Column2, Column3, ColumnX 
1, Value11, Value12, Value13, NULL
1, Value11, Value12, Value13, Value1X1<br/>Value1X2<br/>Value1X3<br/>Value1X4
2, Value21, Value22, Value23, NULL
2, Value21, Value22, Value23, Value2X1<br/>Value2X2<br/>Value2X3<br/>Value2X4

Thanks again,
Denny