Click here to Skip to main content
15,919,931 members
Please Sign up or sign in to vote.
5.00/5 (1 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
Updated 4-Oct-12 20: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

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:

SQL
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
SQL
FOR MemberID IN (SELECT DISTINCT MemberID FROM Members)

won't work.
 
Share this answer
 
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 :)
Hi Sofia,

I think this code suits your question

SQL
--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
 
Share this answer
 
try this
SQL
select distinct Urn, (select Membername + ' ' as [text()] from tbl where URN=urn for xml path('')) as memberName 
from tbl

Happy Coding!
:)
 
Share this answer
 
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?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900