查询与获取一行中的不同行相关。 [英] Query Related to fetching different rows in one row.

问题描述

Hello Experts，



我遇到了困难，需要你的帮助才能创建一个SQL查询。



我首先尝试解释虚拟数据库表设计，然后陈述我的问题。

 ============== ========================================== 
 Sr.No | | FirstName ||姓氏||详情
 ------------------------------------------ 
 1 ||杰克||肖恩||是男性
 2 ||杰克||肖恩||结婚
 3 ||杰克||肖恩||有一辆车
 4 ||杰克||麻雀||是电影明星
 6 ||吉米||肖恩||是男性
 7 || Hritik || Roshan ||结婚
 8 ||汤姆|| Cruize ||正在推出一部电影
 9 ||汤姆|| Cruize ||是好莱坞演员
 ========================================== ================ 

我的问题如下所述。

我想制作一个查询，它选择具有唯一名称的每一行，并在一行中显示其详细信息。



让我详细解释一下。

根据那个SQL查询，我应该得到如下所示的结果。

 =========== $============================================== 
 Sr.No || FirstName ||姓氏||详情
 ------------------------------------------ 
 1 ||杰克||肖恩||是男性，已婚，有车
 2 ||杰克||麻雀||是一部电影明星
 3 ||吉米||肖恩||是男性
 4 || Hritik || Roshan ||结婚
 5 ||汤姆|| Cruize ||正在推出一部电影，是一位好莱坞演员
 ===================================== ===================== 

FirstName ， LastName 和详细信息合并在一行中用逗号分隔。



有谁能帮助我实现这个目标？



提前谢谢。

解决方案

使用sqlservercentral [ ^ ]以下作品......

  CREATE   TABLE  #MyResults（
 [SrNo] [ int ]  IDENTITY （ 1 ， 1 ） PRIMARY   KEY   NOT   NULL ，
 [FirstName] [ varchar ]（ 25 ） NULL ，
 [LastName] [ varchar ]（ 25 ） NULL ，
 [详情] [ varchar ]（ 1000 ）
）
 
  DECLARE  myCur  CURSOR   FOR   SELECT  FirstName，LastName 来自 #MyStatus 组  by  LastName，FirstName 
  DECLARE   @ SrNo   int  
  DECLARE   @ FirstName   varchar （< span class =code-digit> 25 ）
  DECLARE   @ LastName   varchar （ 25 ）
  DECLARE   @ Details   varchar （ 25 ）
  DECLARE   @ DetailList   VARCHAR （ 1000 ）
 
  OPEN  myCur 
  FETCH  NEXT 来自 myCur  INTO   @ FirstName ， @ LastName  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
  SET   @ DetailList  = ' ' 
   -   建立以逗号分隔的详细信息列表 
  SELECT   @ DetailList  = ISNULL（ @ DetailList ，' '）+ [详情] + ' ，'  FROM  #MyStatus 
  WHERE  FirstName =  @FirstName  和 LastName =  @ LastName  
   -   取最后一个逗号 
  SET   @ DetailList  = SUBSTRING（ @ DetailList ， 1 ，LEN（ @ De tailList ） -  1）
 
  INSERT   INTO  #MyResults < span class =code-keyword> VALUES （ @ FirstName ， @ LastName ，< span class =code-sdkkeyword> @ DetailList ）
  FETCH  NEXT 来自 myCur  INTO   @ FirstName ， @ LastName  
  END  
  CLOSE  myCur 
  DEALLOCATE  myCur 
 选择 * 来自 #MyResults 

我使用临时表来查看结果只是因为您的预期结果已编号且我没有放入||请注意，在声明之前，您还应该对 myCur 的存在和可能的重新分配进行一些检查，同样地，对于临时表的存在和可能的删除也是如此。 。



上面的sql结果是

 SrNo FirstName LastName详情
 1 Tom Cruize正在推出一部电影，是好莱坞演员
 2 Hritik Roshan结婚
 3 Jack Sean是男性，已婚，有车
 4 Jimmy Sean是男性
 5 Jack Sparrow是一个电影明星

我应该提到我为此使用了SQL Server 2008.



这里有一些关于T-SQL中CURSOR的更多阅读（它们并不总是好事!!）http://www.sqlservercentral.com/articles/cursors/65136/ [ ^ ]

在这里你去:)

声明@table表
（
 SrNo int，
 FirstName nvarchar（20），
 LastName nvarchar（20），
详情nvarchar（200）
）
 
插入@table值（1，''杰克'' ，''Sean''，''是男性''）
插入@table值（2，''Jack''，''Sean''，''已婚''）
插入@table值（3，''杰克''，''肖恩''，''有车''）
插入@table值（4，''杰克''，''麻雀' '，''是电影明星''）
插入@table值（5，''Jimmy''，''Sean''，''是男性'）
插入@table价值观（6，''Hritik''，''Roshan''，''结婚''）
插入@table值（7，''Tom''，''Cruize''，''是推出电影''）
插入@table值（8，''汤''，''Cruize''，''是好莱坞演员''）
 
 select FirstName，LastName，
 STUFF（（
 select''，''+ second_table.Details 
 from @table second_table 
 WHERE first_table.FirstName = second_table.FirstName and first_table.LastName = second_table.LastName 
 for xml path（'''）），1,1，''''）AS Details 
 from @table first_table 
 group by FirstName，LastName 

Hello Experts,

I am stuck with something and need your help to create an SQL query.

I would first try to explain the dummy Database Table design and then state my question.

========================================================
Sr.No || FirstName || LastName || Details
------------------------------------------
  1   || Jack      || Sean     || is a male
  2   || Jack      || Sean     || is  married
  3   || Jack      || Sean     || has a car
  4   || Jack      || Sparrow  || is a filmstar
  6   || Jimmy     || Sean     || is a male
  7   || Hritik    || Roshan   || is  married
  8   || Tom       || Cruize   || is launching a movie
  9   || Tom       || Cruize   || is a hollywood actor
==========================================================

My Question is as mentioned below.
I want to make a query which selects each row with unique name and displays its details in one single row.

Let me explain in more detail.
According to that SQL query , I should get the result as shown below.

==========================================================
Sr.No || FirstName || LastName || Details
------------------------------------------
  1   || Jack      || Sean     || is a male , is married , has a car
  2   || Jack      || Sparrow  || is a filmstar
  3   || Jimmy     || Sean     || is a male
  4   || Hritik    || Roshan   || is  married
  5   || Tom       || Cruize   || is launching a movie , is a hollywood actor
==========================================================

The FirstName, LastName and Details are merged in one single row seperated by comma.

Can anyone help me in achieving this ??

Thanks in advance.

解决方案

Using a technique seen on sqlservercentral[^] the following works...

CREATE TABLE #MyResults(
 [SrNo] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [FirstName] [varchar](25) NULL,
 [LastName] [varchar] (25) NULL,
 [Details] [varchar] (1000)
)

DECLARE myCur CURSOR FOR SELECT FirstName, LastName from #MyStatus Group by LastName,FirstName
DECLARE @SrNo int
DECLARE @FirstName varchar(25)
DECLARE @LastName varchar(25)
DECLARE @Details varchar(25)
DECLARE @DetailList VARCHAR(1000)

OPEN myCur
FETCH NEXT from myCur INTO @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DetailList = ''
    -- Build up the comma separated list of details
    SELECT @DetailList = ISNULL(@DetailList,'') + [Details] + ',' FROM #MyStatus
    WHERE FirstName = @FirstName and LastName = @LastName
    -- take the last comma off the end
    SET @DetailList = SUBSTRING(@DetailList, 1, LEN(@DetailList)-1)

    INSERT INTO #MyResults VALUES(@FirstName, @LastName, @DetailList)
    FETCH NEXT from myCur INTO @FirstName, @LastName
END
CLOSE myCur
DEALLOCATE myCur
select * from #MyResults

I use a temporary table for the results simply because your expected results were numbered and I haven''t put the || delimiters in. Note that you should also do some checks for the existence of, and possible deallocation of myCur before declaring it, and similarly for the existence and possible dropping of the temporary table.

Results from the sql above is

SrNo	FirstName	LastName	Details
1	Tom	Cruize	is launching a movie,is a hollywood actor
2	Hritik	Roshan	is married
3	Jack	Sean	is a male,is married,has a car
4	Jimmy	Sean	is a male
5	Jack	Sparrow	is a filmstar

[Edit] I should have mentioned that I used SQL Server 2008 for this.

Here is some more reading on CURSOR in T-SQL (they''re not always a good thing!!) http://www.sqlservercentral.com/articles/cursors/65136/[^]

here you go :)

declare @table table
(
    SrNo int,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    Details nvarchar(200)    
)
 
insert into @table values(1, ''Jack'', ''Sean'', ''is a male'')
insert into @table values(2, ''Jack'', ''Sean'', ''is married'')
insert into @table values(3, ''Jack'', ''Sean'', ''has a car'')
insert into @table values(4, ''Jack'', ''Sparrow'', ''is a filmstar'')
insert into @table values(5, ''Jimmy'', ''Sean'', ''is a male'')
insert into @table values(6, ''Hritik'', ''Roshan'', ''is married'')
insert into @table values(7, ''Tom'', ''Cruize'', ''is launching a movie'')
insert into @table values(8, ''Tom'', ''Cruize'', ''is a hollywood actor'')

select FirstName, LastName, 
	STUFF((
        select '','' + second_table.Details
        from @table second_table
        WHERE first_table.FirstName = second_table.FirstName and first_table.LastName = second_table.LastName
        for xml path('''')),1,1,'''') AS Details
from @table first_table
group by FirstName, LastName

这篇关于查询与获取一行中的不同行相关。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！