Concatenate multiple rows in one column

Question

I have a query that produces the following results

NAME         WeekPattern

John Smith   
John Smith   //
John Smith   OO
Jack Jones   
Jack Jones   O
Jack Jones   //

Is there a way I can concatenate the WeekPattern column so the results will show like this:

NAME         WeekPattern

John Smith     // OO
Jack Jones     O //

I have tried using FOR XML PATH but couldn't get it to work, here is the query for my attempt:

SELECT 
p.p_surname,
p.p_forenames,
LEFT(sr.sr_weekpattern , LEN(sr.sr_weekpattern )-1) AS Weekpattern
FROM 
unitesnapshot.dbo.capd_studentregister sr
INNER JOIN unitesnapshot.dbo.capd_person p ON p.p_id = sr.sr_student
CROSS APPLY
(
    SELECT sr.sr_weekpattern + ' '
    FROM unitesnapshot.dbo.capd_studentregister sr1
    INNER JOIN unitesnapshot.dbo.capd_person p1 ON p1.p_id = sr1.sr_student

    WHERE p.p_id = p1.p_id
    FOR XML PATH('')
) pre_trimmed (Weekpattern)
GROUP BY p.p_surname, p.p_forenames, sr.sr_weekpattern;

I get this error when running this query:

Invalid length parameter passed to the LEFT or SUBSTRING function.

Any help would be much appreciated

John Woo · Accepted Answer · 2013-02-28 15:22:29Z

2

SELECT
     Name,
     STUFF(
         (SELECT ' ' + WeekPattern
          FROM TableName
          WHERE NAME = a.NAME AND WeekPattern IS NOT NULL
          FOR XML PATH (''))
          , 1, 1, '')  AS WeekPatternList
FROM TableName AS a
GROUP BY Name

SQLFiddle Demo

answered Feb 28, 2013 at 15:22

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

can you give sample records along with your question?

Taryn · Accepted Answer · 2013-02-28 15:45:26Z

1

Based on your existing query, you are not referencing the correct column in the LEFT, you should be using:

SELECT 
  p.p_surname,
  p.p_forenames,
  LEFT(pre_trimmed.Weekpattern , LEN(pre_trimmed.Weekpattern)-1) AS Weekpattern
FROM unitesnapshot.dbo.capd_studentregister sr
INNER JOIN unitesnapshot.dbo.capd_person p 
  ON p.p_id = sr.sr_student
CROSS APPLY
(
  SELECT sr1.sr_weekpattern + ' '
  FROM unitesnapshot.dbo.capd_studentregister sr1
  INNER JOIN unitesnapshot.dbo.capd_person p1 
    ON p1.p_id = sr1.sr_student
  WHERE p.p_id = p1.p_id
  FOR XML PATH('')
) pre_trimmed (Weekpattern)

edited Feb 28, 2013 at 15:45

answered Feb 28, 2013 at 15:26

Taryn

249k57 gold badges374 silver badges409 bronze badges

5 Comments

Will Over a year ago

This answer works but only for half a second then it gives the error:Invalid length parameter passed to the LEFT or SUBSTRING function.

Taryn Over a year ago

@Will does the subquery work on its own if you remove the WHERE p.p_id = p1.p_id?

Taryn Over a year ago

@Will the subquery doesn't have the left so how does that throw an error? Does the subquery inside of the cross apply work on its own?

Will Over a year ago

sorry, when WHERE p.p_id = p1.p_id the same error is thrown. When the subquery is run by itself it wont run because p.p_id and sr.sr_weekpattern are out of scope

Taryn Over a year ago

@Will you have to remove that where clause and inside of that subquery it should be select sr1.sr_weekpattern + ' ' as I changed in my answer.

Collectives™ on Stack Overflow

Concatenate multiple rows in one column

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related