CODEWITHSUNDEEP
sqlsql-server-2008concatenation
Tone
Tone

Reputation: 783

Concatenate rows of same record to one row

Hello I need to include all the assignments of an employee in the same row. Some of the employees have 1 assignment and some have 3 or more. This is to go into a sproc and bring back all the assignments that are under that record.

Example of record in database w/ 3 records:
John doe - assign1
John doe - assign2
John doe - assign3

Desired Result:
3, assign1, assign2, assign3

Example of record in database w/ 1 record:
John doe - assign1

Desired Result:
1, assign1

This is what I have so far but can't get it to work.

    SELECT cast(count(*) as varchar(10))+', ' + min([Assign1])+', '+
max([Assign1]), max([Assign1])
From assignment
where year = '2012'
and first_name = 'firstname'
and last_name = 'lastname'

Here is the dumb downed Table structure:

Create INTO [database].[dbo].[assignment]
           ([employee_key]
           ,[last_name]
           ,[first_name]
           ,[assignment_code]
           ,[assignment_desc] )

Upvotes: 0

Views: 471

Answers (1)

sgeddes
sgeddes

Reputation: 62831

I would look into using the STUFF command. This could be optimized if you have an ID column (need your table schema), but given your above code, something like this:

Select cast(count(*) as varchar) + ', ' +
   stuff((select  ', '+A2.Assign1
         from Assignment A2
         where year = '2012'
             and first_name = 'john'
             and last_name = 'doe'
         for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')
FROM Assignment A1
WHERE year = '2012'
   and first_name = 'john'
   and last_name = 'doe'

And the SQL Fiddle.

Here is without hard coding the WHERE clause twice:

Select count(*), 
   stuff((select  ', '+A2.Assign1
         from Assignment A2
         where A2.Year = A1.Year and 
            A2.First_Name = A1.First_Name and 
            A2.Last_Name = A1.Last_Name
         for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')
FROM Assignment A1
WHERE year = '2012'
    and first_name = 'john'
    and last_name = 'doe'
GROUP BY year, first_name, last_name

Good luck.

Upvotes: 1

Related Questions