Click here to Skip to main content
12,547,616 members (44,434 online)
Rate this:
Please Sign up or sign in to vote.
Hi All,
I saved Values like this

1 AndhraPradesh 22 Vizianagaram Denkada
1 AndhraPradesh 22 Vizianagaram Bhoghapuram
1 AndhraPradesh 22 Vizianagaram Pusapatirega
1 AndhraPradesh 21 Visakhapatnam Bheemunipatnam
1 AndhraPradesh 21 Visakhapatnam Araku Valley
1 AndhraPradesh 21 Visakhapatnam Anakapalle
1 AndhraPradesh 21 Visakhapatnam Gajuwaka
1 AndhraPradesh 21 Visakhapatnam Chodavaram

But i want output like this

1 AndhraPradesh 22 Vizianagaram Denkada
21 Visakhapatnam Araku Valley

is it possible r not?
can any one solve it plz
Thanks & Regards
Posted 27-Nov-12 1:07am
Updated 27-Nov-12 2:13am
Krunal R 27-Nov-12 8:04am
What have you tried ? And you want column name or mentioned value ??
Rate this: bad
Please Sign up or sign in to vote.

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:[^]

The statement from that post that would probably be what you want is this one:
FROM #TableParent p
             FROM #TableChild
             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: bad
Please Sign up or sign in to vote.

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.161018.1 | Last Updated 28 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100