Rate this:
See more:
Hi All,
I saved Values like this

1 AndhraPradesh 21 Visakhapatnam Araku Valley

But i want output like this

Bhogapuram
Pusapatirega
21 Visakhapatnam Araku Valley
Gajuwaka

is it possible r not?
can any one solve it plz
Thanks & Regards
Hari
Posted 27-Nov-12 2:07am
Edited 27-Nov-12 3:13am
v3
Krunal R at 27-Nov-12 8:04am

What have you tried ? And you want column name or mentioned value ??

Rate this:

## Solution 1

Based upon your illustration, I think the answer you are looking for is a qualified "sort of". What it looks like you want is a new row with just the last column's data when the first part is duplicated. Sure, you could blank the other columns out but what's the point. Besides, the SQL statement to do so would be ugly. However, if you want all of the values for that last column to be in one column (so each person gets one row and the last column has n number of entries in it), you can do so using a CROSS APPLY statement. Here is a forum post that will help you out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102429[^]

The statement from that post that would probably be what you want is this one:
```SELECT p.ASSIGNNUM,p.DESC,p.STARTDATE,LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM #TableParent p
CROSS APPLY (SELECT EMPLOYEENUM + ',' AS [text()]
FROM #TableChild
WHERE ASSIGNNUM =p.ASSIGNNUM
FOR XML PATH('')) el(EmpList)```
Obviously you would need to modify it to fit your needs but the end result would be a comma-delimited list of values in the last column.
Rate this:

## Solution 2

This is my solution:

```Select Distinct o.State, o.City,

(

Select I.Mandal + ', ' From Mandal I

Where I.StateID = o.StateID and I.CityID = o.CityID

For XML Path('')

) [Combined Data]

from Country O```