Stuck in SQL QUERY......................................................................................

Question

5.00/5 (1 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 20:04pm

sofia3

Updated 4-Oct-12 20:10pm

v4

Add a Solution

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

3 solutions

Solution 4

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

Posted 5-Oct-12 19:46pm

damodara naidu betha

Solution 1

try this

SQL

select distinct Urn, (select Membername + ' ' as [text()] from tbl where URN=urn for xml path('')) as memberName 
from tbl

Happy Coding!
:)

Posted 4-Oct-12 20:10pm

Aarti Meswania

Updated 4-Oct-12 20:15pm

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

skydger · Accepted Answer · 2012-10-04T20:14:00

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.