Update rows group by columns

Question

I have a table with 4 columns:

| PromoId | Amount| PromoType  |Description|
--------------------------------------------------
|   101   |   11  |      a     |     free|       
|   101   |   12  |      a     |     20% |       
|   103   |   17  |      c     |     45% |       
|   104   |   14  |      c     |     50% |

I have to combine the description for the same value of PromoId and PromoType .

For aforementioned table my output should be like :

| PromoId | Amount| PromoType  |Description|
--------------------------------------------------
|   101   |   11  |      a     |     free 20% |       
|   101   |   12  |      a     |     free 20% |       
|   103   |   17  |      c     |     45%      |       
|   104   |   14  |      c     |     50%      |

I am using SQL Server. Thanks in advance.

When you want to sum a text field, you know that something went wrong in your design. What rdbms are you using? — Tim Schmelter, Jan 15 '13 at 14:01
sql. More over I am writing a stored procedure and I have to do it in a temporary table. I have this option of using loop.But it's degrading the performance. — palak mehta, Jan 15 '13 at 14:03
Description is just a field of type varchar. I am using SQL Server. — palak mehta, Jan 15 '13 at 14:06
It looks like you just want to concatenate identical fields. see http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql or http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings — Scotch, Jan 15 '13 at 14:11

score 1 · Answer 1 · answered Jan 15 '13 at 14:20

WITH ConcatValue
AS
(
  SELECT
       PromoId,
       STUFF(
           (SELECT ' ' + Description
            FROM TableName
            WHERE PromoId = a.PromoId
            FOR XML PATH (''))
            , 1, 1, '')  AS Title
  FROM TableName AS a
  GROUP BY PromoId
)
SELECT   a.PromoId, a.Amount, a.PromoType,
         b.Title
FROM     tableName a
         INNER JOIN ConcatValue b
            ON a.PromoId = b.PromoId

SQLFiddle Demo

score 1 · Answer 2 · edited May 23 '17 at 12:18

1

Here is my solution

select d.PromoId, d.Amount, d.PromoType, Left(x.Description,Len(x.Description)-1)
from demo d
join (
    select distinct x1.PromoId, 
    (select x2.Description + ',' from demo x2 where x2.PromoId = x1.PromoId For XML PATH ('')) Description
    from demo x1) x on d.PromoId = x.PromoId

Acknowledgements

edited May 23 '17 at 12:18

Community

1
1

answered Jan 15 '13 at 14:24

alexb

971
6
12

score 0 · Answer 3 · answered Jan 15 '13 at 14:12

You need string concatenation, which is a little cumbersome in SQL Server. Here is one way:

select t.*,
       (select t2.description+' '
        from t t2
        where t2.promoID = t.promoID and t2.promoType = t.promotType
        order by t2.amount
        for xml path ('')
       ) as CombinedDescription
from t

This leaves a space at the end, which you can trim off.

Update rows group by columns

3 Answers3