469,575 Members | 1,598 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

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

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.

Expand|Select|Wrap|Line Numbers
  1. DECLARE @Table1 TABLE
  2. (
  3.     ID int,
  4.     Column1 nvarchar(10),
  5.     Column2 nvarchar(10),
  6.     Column3 nvarchar(10),
  7.     ColumnX nvarchar(1000)
  8. )
  10. INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', NULL)
  11. INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX1')
  12. INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX2')
  13. INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX3')
  14. INSERT INTO @Table1 VALUES (1, 'Value1', 'Value2', 'Value3', 'ValueX4')
The Desired Result Is:
Expand|Select|Wrap|Line Numbers
  1. ID, Column1, Column2, Column3, ColumnX
  2.  1, 'Value1', 'Value2', 'Value3', NULL
  3.  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,
Feb 1 '08 #1
2 5455
1,134 Expert 1GB
Yes, it would be nice to be able to say

Expand|Select|Wrap|Line Numbers
  1.    SELECT ID,Column1,Coulmn2,Column3,concat(Columnx) as Columnx
  2.    FROM @Table1 
  3.    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
Expand|Select|Wrap|Line Numbers
  1. SELECT ID,Column1,Coulmn2,Column3,Max(ColumnX1) + ',' + Max(ColumnX2) + etc etc etc as Columnx 
  2. FROM
  3. (   SELECT ID,Column1,Coulmn2,Column3
  4.                 ,case when SeqNo=1 then ColumnX else'' end as ColumnX1
  5.                 ,case when SeqNo=2 then ColumnX else'' end as ColumnX2
  6.                 , etc etc etc
  7.     FROM
  8.     @Table1
  9. )a 
  10. 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
Feb 4 '08 #2
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:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @Table1 TABLE
  2. (
  3.     RowCounter int identity (1,1),
  4.     ID int,
  5.     Column1 nvarchar(10),
  6.     Column2 nvarchar(10),
  7.     Column3 nvarchar(10),
  8.     ColumnX nvarchar(10)
  9. )
  11. INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', NULL)
  12. INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X1')
  13. INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X2')
  14. INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X3')
  15. INSERT INTO @Table1 VALUES (1, 'Value11', 'Value12', 'Value13', 'Value1X4')
  16. INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', NULL)
  17. INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X1')
  18. INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X2')
  19. INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X3')
  20. INSERT INTO @Table1 VALUES (2, 'Value21', 'Value22', 'Value23', 'Value2X4')
  22. DECLARE    @iCounter INT,
  23.     @iLoop INT, 
  24.     @iID INT
  26. DECLARE @Result TABLE
  27. (
  28.     ID int,
  29.     Column1 nvarchar(10),
  30.     Column2 nvarchar(10),
  31.     Column3 nvarchar(10),
  32.     ColumnX nvarchar(1000)
  33. )
  35. SET @iCounter = 1
  36. SELECT @iLoop = Max(RowCounter) FROM @Table1
  38. WHILE @iCounter <= @iLoop
  39. BEGIN
  40. SELECT @iID = ID FROM @Table1 WHERE RowCounter = @iCounter
  42. IF (NOT EXISTS (SELECT * FROM @Result WHERE ID = @iID AND ColumnX IS NOT NULL) --This ID is not yet in result table
  43.     OR EXISTS(SELECT * FROM @Table1 WHERE RowCounter = @iCounter AND ColumnX IS NULL) --OR the ColumnX is null
  44. )
  45. BEGIN
  46.     INSERT INTO @Result SELECT ID,Column1,Column2,Column3,ColumnX 
  47.         FROM @Table1 WHERE RowCounter = @iCounter --Add Row to result table
  48.     DELETE @Table1 WHERE RowCounter = @iCounter --Remove row from initial table
  49. END
  50. ELSE --A row with ID = @iID and a non-null ColumnX already exists in result table 
  51. BEGIN
  52.     UPDATE @Result SET ColumnX = ColumnX + '<br/>' + (SELECT ColumnX FROM @Table1 WHERE RowCounter = @iCounter)
  53.     FROM @Result WHERE ID = @iID AND ColumnX IS NOT NULL --Update ColumnX
  54.     DELETE @Table1 WHERE RowCounter = @iCounter --Remove row from initial table
  55. END
  57. SET @iCounter = @iCounter + 1
  58. END
  60. SELECT * FROM @Result
The Desired Resulting Table Is:

Expand|Select|Wrap|Line Numbers
  1. ID, Column1, Column2, Column3, ColumnX 
  2. 1, Value11, Value12, Value13, NULL
  3. 1, Value11, Value12, Value13, Value1X1<br/>Value1X2<br/>Value1X3<br/>Value1X4
  4. 2, Value21, Value22, Value23, NULL
  5. 2, Value21, Value22, Value23, Value2X1<br/>Value2X2<br/>Value2X3<br/>Value2X4
Thanks again,
Feb 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Fan Ruo Xin | last post: by
3 posts views Thread by Stephen Matthews | last post: by
14 posts views Thread by imani_technology_spam | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.