How combine multiple cells into a single row in SQL?

Question

I am trying to convert work done MS Excel into t-sql code. Column A is my index. I need to combine the values associate with index =1 in column C, each separated by a soft break.

In MS Excel I achieve this by placing this function in cell C2=IF(A2<>A1,B2,C1 & CHAR(10) & B2) and =IF(A2<>A3,1,0).

The combined functions on column C will concatenate of the values in column B, as the index in column 1 match. The function in column D will compare the values in column C until it finds an unique value. In cell D5, notice values from B2:B5 has been concatenated into a single cell.

If filter the worksheet where the values in column D =1, then I am able to get the desired result.

How can I make this happened in SQL?

what determines the order of your data? Currently, there is no way to guarantee the return of the results to be deterministic — S3S, Sep 18 '18 at 18:22
@user2404597 In excel this can be achieve using [ALT]+[ENTER] — user716255, Sep 18 '18 at 20:18
@scsimon The data will be sorted by No prior to being loaded in SQL. — user716255, Sep 18 '18 at 20:19
that's all fine and dandy, but once it's in sql server it becomes ordered despite *looking* ordered. select without an order by statement doesn't guarantee the results will be returned int he same order. Read this: https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/ which means the accepted answer won't always work even though it may seem to on that small data size. — S3S, Sep 18 '18 at 20:57

score 1 · Answer 1 · answered Sep 18 '18 at 18:28

1

This worked for me, with a caveat:

DECLARE @ TABLE (No int, color VARCHAR(20))
INSERT INTO @(No, color) values

--No  color
(1  ,'blue'),
(1  ,'red'),
(1  ,'pink'),
(1  ,'yellow'),
(2  ,'blue'),
(2  ,'red'),
(2  ,'pink'),
(3  ,'yellow')

SELECT IIF(rn = 1,CAST(No as CHAR(1)), '') AS No, color
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY No ORDER BY (SELECT NULL)) AS rn,
No, color
FROM @
) _

The caveat is what the comment by scsimon is getting at. There is no guarantee that SQL will return the rows in the order you posted.

answered Sep 18 '18 at 18:28

user1443098

6,487
5
38
67

Why does your script end in "_"? – user716255 Sep 18 '18 at 20:31
Last, what you are suggestion is creating '' where rn != 1. I need to concatenate all the strings where No =1, No=2 & No = 3 into a cell. In other words, I need to use something like char(13) and char(10). I tried to modified your code, but I did not provide the desire results. – user716255 Sep 18 '18 at 21:02
your expected results didn't specify this @user716255 and in this case, you need to use this: https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server – S3S Sep 18 '18 at 21:25

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

This is not an elegant solution, but it get the job done. Please feel free to improve on it.

Note:

Carriage return char(13) and line feed char(10) will not work in SSMS. You will be able to see the desired outcome in platforms such as SSRS or Tableau.

select distinct no, 
    color = 
    Stuff((select char(13) + char(10) +t1.color 
    from test t1
    where 
    t1.No = t2.No
    for xml path (''), TYPE).value('.', 'NVARCHAR(Max)'), 1, 1,'')
from test t2

SSMS

  no    color
   1    blue red pink  yellow
   2    blue  red  pink 
   3    yellow

Tableau

  no    color
   1    blue
        red
        pink 
        yellow
   2    blue 
        red 
        pink
   3    yellow

How combine multiple cells into a single row in SQL?

2 Answers2

Note: