卡在SQL QUERY中..................................... ......................................................... [英] Stuck in SQL QUERY......................................................................................

问题描述

URN	                 MemberID     MemberName
----------------------------------------------------------------
00000071093585671	   1	      GIRDHARI
00000071093585671	   2	      NIRALA
00000071093585671	   3	      KALINDI
00000071093585671	   4	      AJAY
00000071093585671	   5	      ADALTI
00000071093587325	   1	      RISHIKH
00000071093587325	   2	      CHANDRAMUKHI

我的数据就像上面.我只是希望查询像这样，这样我才能得到像第一行这样的结果集:GIRDHARI NIRALA KALINDI AJAY ADALTI
第二排:RISHIKH CHANDRAMUKHI


这是基于URN.PLz的帮助我.

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

推荐答案

如果您的MemberID有限，请尝试使用PIVOT运算符.

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

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

仅当在FOR子句中列出了MemberID值范围时，此方法才有效.不多也不少.代码

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.

索非亚，

我认为这段代码适合您的问题

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

尝试

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

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于卡在SQL QUERY中..................................... .........................................................的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！