-1

I am looking to concat a column in each row that that holds text input....

I am using SQL Server 2005

The rows look like this

Number     Date           Update Time     description
------    -----           -----------    -------------
0123      01/01/2015      01/07/2015     Hello, I want to
0123      01/01/2015      01/01/2015     Concat these columns

Hopefully this is easy and I am just being a simpleton

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve Dyson
  • 13
  • 1
  • 1
    can you put a expected sample output? – huMpty duMpty Sep 24 '15 at 09:17
  • What you need is something like MySQL's `GROUP_CONCAT` function, however SQL*Server does not have one - see http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server for some solutions (`STUFF` may work best here). – Raad Sep 24 '15 at 09:20
  • 1
    @Raad STUFF has nothing to do with the actual concatination of the columns, STUFF is a function to replace characters at a position in a varchar – t-clausen.dk Sep 24 '15 at 09:23
  • 2
    How would you determine which description should be the first ? – t-clausen.dk Sep 24 '15 at 09:24
  • @t-clausen.dk - yes, you are correct, however I meant to refer to the answer that uses `STUFF` and now I can't edit my comment =/ – Raad Sep 24 '15 at 09:28
  • The Expected Output would be 1 Row with the description field concatenated..... – Steve Dyson Sep 24 '15 at 09:37
  • @SteveDyson there is no row 1, I see 2 rows which cannot be ordered by any column, a solution cannot be garantied to pick the first row on your list as the first row – t-clausen.dk Sep 24 '15 at 11:23
  • @t-clausen.dk, I would Like the 2 rows to Become 1 row with the description field Concatenated.... hope this explains it better – Steve Dyson Sep 25 '15 at 10:11

2 Answers2

1

You could use this approach:

SELECT A.Number, MAX(A.[Date]) AS [Date], MAX(A.[Update Time]) AS [Update Time]
, STUFF((SELECT ' ' + B.description AS [text()] 
    FROM table1 B 
        WHERE A.Number = B.Number 
            FOR XML PATH('')), 1, 1, '' ) AS Description
FROM  table1 A
GROUP BY A.Number
John Bell
  • 2,350
  • 1
  • 14
  • 23
  • Thank You John, i will give this a try.... Can i also use a where clause on the Date because i need to filter by another field that has either Open or Closed in it... – Steve Dyson Sep 24 '15 at 09:40
  • Not completely sure of what you mean. But because it's matching on the Number field, as long as you filter the outer select with the data you need first with a `WHERE` clause then you'll be fine. – John Bell Sep 24 '15 at 09:42
1

Because you are dealing free form text, I think it is better to explicitly convert the XML back to a character data type:

SELECT t.Number,
       STUFF((SELECT ' ' + cast(t2.description AS nvarchar(max) )
              FROM <tablen> t2 
              WHERE t2.Number = t.Number 
              FOR XML PATH(''), TYPE
             ).VALUE('.', 'nvarchar(max)'
                    ), 1, 1, '' ) AS Description
FROM <table> t
GROUP BY t.Number;

This prevents problems with characters such as &, <, and >.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786