asked on

SQL Server : Concatenate the values of the same column to get one column values

Hi I want to build a query that allows me to concatenate the differents values of the same column for the same line ID .... it is a kind of Group By ......

Attached you will see the view that I built and the view lines ..... Also I attached a code I found within some searches on the web but I didn't figure out how to adapt it to my case.

The final result should be a view that will get for each LineID ------> Value1, Value2, Valuex and so on .....
Picture-one.jpg
picture-two.jpg
picture-three.jpg

sammySeltzer

If I understand you correctly, this is what you need.

In your case, you can separate the values by comma or whatever your requirements are.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

sammySeltzer

This is the example code I used from that link, the part that says use when values are unknown.

WITH CTE ( Id, employee_id, charity_code, length ) 

          AS ( SELECT Id, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0

                 FROM employee_ded_amts

                GROUP BY Id

                UNION ALL

               SELECT p.Id, CAST( p.employee_id + '++'+

                      CASE WHEN length = 0 THEN '' ELSE ', ' END + p.charity_code AS VARCHAR(8000) ), 

                      CAST( p.charity_code AS VARCHAR(8000)), length + 1

                 FROM CTE c

                INNER JOIN employee_ded_amts p

                   ON c.Id = p.Id

                WHERE p.charity_code > c.charity_code )

SELECT Id, employee_id 

      FROM ( SELECT Id, employee_id, 

                    RANK() OVER ( PARTITION BY Id ORDER BY length DESC )

               FROM CTE ) D ( Id, employee_id, rank )

     WHERE rank = 1 ;

Open in new window

I used ++ as delimiter and I got values like this screenshot.

In my example, I concatenated only 2 column names and I used ID as identifier.

singlerow.JPG

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

venmarces

ASKER

Thanks