Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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
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
Comments
Krunal R at 27-Nov-12 8:04am
   
What have you tried ? And you want column name or mentioned value ??
Rate this: bad
good
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:
 
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.
  Permalink  
Rate this: bad
good
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
  Permalink  

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



Advertise | Privacy | Mobile
Web04 | 2.8.150327.1 | Last Updated 28 Nov 2012
Copyright © CodeProject, 1999-2015
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