Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
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
sofia3727
Edited 4-Oct-12 21:10pm
v4
Comments
Sandeep Mewara at 5-Oct-12 2:13am
   
I doubt if a single simple query can do it. What have you tried so far?
sofia3 at 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 at 5-Oct-12 2:38am
   
no member id can be random,plz help
sofia3 at 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 at 5-Oct-12 3:21am
   
Please see updated solution
sofia3 at 5-Oct-12 4:45am
   
thnx a lot,it helped me a lot:)
skydger at 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 at 5-Oct-12 2:13am
   
Its coming in one row i want different rows on the basis of URN
sofia3 at 5-Oct-12 2:14am
   
and i dont want comma in between names
aarti meswania at 5-Oct-12 2:16am
   
please see updated solution, see underline portion in query put anything in that Single quate whitespace,*,&,comma anything
sofia3 at 5-Oct-12 2:24am
   
its taking too much time to execute:(
aarti meswania at 5-Oct-12 2:25am
   
have you applied indexing on urn column?
sofia3 at 5-Oct-12 2:32am
   
no
aarti meswania at 5-Oct-12 2:40am
   
ok then apply indexing in urn column it will make it fast
sofia3 at 5-Oct-12 2:48am
   
I am new to sql plz help how to apply it
sofia3 at 5-Oct-12 2:53am
   
i got that but the result set is not proper
aarti meswania at 5-Oct-12 2:55am
   
it is like below,
00000071093585671 GIRDHARI NIRALA KALINDI ...
you dont want it like this?
sofia3 at 5-Oct-12 3:01am
   
i want all the name should in different columns
aarti meswania at 5-Oct-12 3:05am
   
then go with pivot method in solution 1
sofia3 at 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
0 OriginalGriff 545
1 Maciej Los 475
2 Sascha Lefévre 289
3 CPallini 285
4 Sergey Alexandrovich Kryukov 274
0 Sergey Alexandrovich Kryukov 9,897
1 OriginalGriff 9,395
2 Peter Leow 5,162
3 Kornfeld Eliyahu Peter 3,373
4 Maciej Los 3,036


Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 6 Oct 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