Click here to Skip to main content
12,241,975 members (39,904 online)
Rate this:
 
Please Sign up or sign in to vote.
URN	                 MemberID     MemberName
----------------------------------------------------------------
00000071093585671	   1	      GIRDHARI
00000071093585671	   2	      NIRALA
00000071093585671	   3	      KALINDI
00000071093585671	   4	      AJAY
00000071093585671	   5	      ADALTI
00000071093587325	   1	      RISHIKH
00000071093587325	   2	      CHANDRAMUKHI

My data is like above.I simply want my query to be like such that i get result set like First row: GIRDHARI NIRALA KALINDI AJAY ADALTI
Second Row: RISHIKH CHANDRAMUKHI


that is on the basis of URN.PLz help me
Posted 4-Oct-12 21:04pm
sofia3673
Edited 4-Oct-12 21:10pm
v4
Comments
Sandeep Mewara 5-Oct-12 2:13am
   
I doubt if a single simple query can do it. What have you tried so far?
sofia3 5-Oct-12 3:05am
   
SELECT 'MemberName' AS MemberName,
[0], [1], [2], [3], [4]
FROM
(SELECT MemberName, URN
FROM TxnDependents) AS SourceTable
PIVOT
(
URN FOR MemberName IN ([0], [1], [2], [3], [4])
) AS PivotTable;

trying this but not getting
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

If you have limited MemberID's, try to use PIVOT operator.

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

Ok, here the complete solution.
While PIVOT can be applied only for those columns to which could be applied agregate functions, you have to use some trick like the following:

SELECT pt.URN,
       ISNULL( (SELECT MemberName FROM Members WHERE Members.URN = pt.URN AND MemberID = pt.[1] ), '') AS nm1,
       ISNULL( (SELECT MemberName FROM Members WHERE Members.URN = pt.URN AND MemberID = pt.[2] ), '') AS nm2,
       ISNULL( (SELECT MemberName FROM Members WHERE Members.URN = pt.URN AND MemberID = pt.[3] ), '') AS nm3,
       ISNULL( (SELECT MemberName FROM Members WHERE Members.URN = pt.URN AND MemberID = pt.[4] ), '') AS nm4,
       ISNULL( (SELECT MemberName FROM Members WHERE Members.URN = pt.URN AND MemberID = pt.[5] ), '') AS nm5
  FROM
       (SELECT URN, MemberID
          FROM Members ) AS src
 PIVOT ( MAX(MemberID)FOR MemberID IN ([1], [2], [3], [4], [5]) ) AS pt
This will work only if you have a MemberID values range listed in FOR clause. No more and no less. The code
FOR MemberID IN (SELECT DISTINCT MemberID FROM Members)
won't work.
  Permalink  
v3
Comments
sofia3 5-Oct-12 2:38am
   
no member id can be random,plz help
sofia3 5-Oct-12 3:06am
   
SELECT 'MemberName' AS MemberName,
[0], [1], [2], [3], [4]
FROM
(SELECT MemberName, URN
FROM TxnDependents) AS SourceTable
PIVOT
(
URN FOR MemberName IN ([0], [1], [2], [3], [4])
) AS PivotTable;
skydger 5-Oct-12 3:21am
   
Please see updated solution
sofia3 5-Oct-12 4:45am
   
thnx a lot,it helped me a lot:)
skydger 5-Oct-12 4:57am
   
You're welcome :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Hi Sofia,

I think this code suits your question

--temporary table to store sample data
declare @t table([URN] varchar(max), MemeberId int,Name Varchar(50))
 
-- Insert sample data into the temporary table
insert @t values ('00000071093585671',1,'GIRDHARI'),
('00000071093585671',2,'NIRALA'),
('00000071093585671',3,'KALINDI'),
('00000071093585671',4,'AJAY'),
('00000071093585671',5,'ADALTI'),
('00000071093587325',1,'RISHIKH'),
('00000071093587325',2,'CHANDRAMUKHI')
 
--Query to get the result.
select t.URN
    ,STUFF((
        select ',' + [Name]
        from @t t1
        where t1.URN = t.URN
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') [values]
from @t t
group by t.URN
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

try this
select distinct Urn, (select Membername + ' ' as [text()] from tbl where URN=urn for xml path('')) as memberName 
from tbl
Happy Coding!
Smile | :)
  Permalink  
v3
Comments
sofia3 5-Oct-12 2:13am
   
Its coming in one row i want different rows on the basis of URN
sofia3 5-Oct-12 2:14am
   
and i dont want comma in between names
aarti meswania 5-Oct-12 2:16am
   
please see updated solution, see underline portion in query put anything in that Single quate whitespace,*,&,comma anything
sofia3 5-Oct-12 2:24am
   
its taking too much time to execute:(
aarti meswania 5-Oct-12 2:25am
   
have you applied indexing on urn column?
sofia3 5-Oct-12 2:32am
   
no
aarti meswania 5-Oct-12 2:40am
   
ok then apply indexing in urn column it will make it fast
sofia3 5-Oct-12 2:48am
   
I am new to sql plz help how to apply it
sofia3 5-Oct-12 2:53am
   
i got that but the result set is not proper
aarti meswania 5-Oct-12 2:55am
   
it is like below,
00000071093585671 GIRDHARI NIRALA KALINDI ...
you dont want it like this?
sofia3 5-Oct-12 3:01am
   
i want all the name should in different columns
aarti meswania 5-Oct-12 3:05am
   
then go with pivot method in solution 1
sofia3 5-Oct-12 3:02am
   
currently it is coming in a single rows and concanating all the member name of table

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
Web01 | 2.8.160426.1 | Last Updated 6 Oct 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