By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

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

denny1824
P: 32
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:
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. )
  9.  
  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')
  15.  
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'
  4.  
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
Feb 1 '08 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
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
  4.  
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
  11.  
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

denny1824
P: 32
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. )
  10.  
  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')
  21.  
  22. DECLARE    @iCounter INT,
  23.     @iLoop INT, 
  24.     @iID INT
  25.  
  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. )
  34.  
  35. SET @iCounter = 1
  36. SELECT @iLoop = Max(RowCounter) FROM @Table1
  37.  
  38. WHILE @iCounter <= @iLoop
  39. BEGIN
  40. SELECT @iID = ID FROM @Table1 WHERE RowCounter = @iCounter
  41. IF @iID IS NOT NULL
  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
  56.  
  57. SET @iCounter = @iCounter + 1
  58. END
  59.  
  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,
Denny
Feb 4 '08 #3

Post your reply

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