Click here to Skip to main content
12,763,515 members (33,572 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
Updated 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!
:)
  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
sofia3 5-Oct-12 3:02am
   
currently it is coming in a single rows and concanating all the member name of table
aarti meswania 5-Oct-12 3:05am
   
then go with pivot method in solution 1

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.170217.1 | Last Updated 6 Oct 2012
Copyright © CodeProject, 1999-2017
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