Merge Rows in Sql Server

Posted on 2012-12-23
Medium Priority
Last Modified: 2013-08-14
Hi Expert,

I have provided sample table in sql server :

ID      Name      Dep                Add
1      A               N                A-1
1      B               K                A-1
1      C               M                A-1
2      D               Z                A-2
2      E               O                A-2

Note: Above rows can be dynamic, it is not static like ID = 1 can have 3 rows as stated above or it could have more  or less than 3 as well.

Expected output is:
ID      Name1      Name2      Name3      Dep1      Dep2      Dep3      Add
1      A                  B                 C                N                K                M              A-1
2      D                  E               NULL        Z                O              NULL        A-2

Hopefully looking forward to hearing from you.
Question by:satmisha
  • 3
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 700 total points
ID: 38716880
so, you need a "dynamic crosstab" function:

Author Comment

ID: 38717003
Thanks for your reply..
seems like it is not working as it is expecting columns as int whereas in above stated example it is varchar...
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38717014
I did not test the function, the key for you is to use "othercolumns" (@OtherCols) argument ...

Author Comment

ID: 38735941
Thanks angelIII,

Apologies for late reply

Above stated didn't work.

But I got the solution by making sql function with special character saparated column.

Author Comment

ID: 38905049
request to pls close this question as provided answer was not appropiate.. n I am unable to see any option to close by my own...


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question