Question

我的表格如下:

ID Name 1 Amit 2 Mohit 2 Nahar

我的乐观态度是提出一个问题,把这一表格更新到以下格式中。

ID Name 1 Amit 2 Mohit OR NAHAR

是否有能够解决这一问题的疑问?

Answer 1

引言

DECLARE @Table TABLE(       
        ID INT,
        NAME VARCHAR(MAX)
)
    INSERT INTO @Table (ID,[NAME]) SELECT 1,  Amit 
INSERT INTO @Table (ID,[NAME]) SELECT 2,  Mohit 
INSERT INTO @Table (ID,[NAME]) SELECT 2,  Nahar 
INSERT INTO @Table (ID,[NAME]) SELECT 3,  C 
INSERT INTO @Table (ID,[NAME]) SELECT 3,  D 
INSERT INTO @Table (ID,[NAME]) SELECT 3,  E

www.un.org/Depts/DGACM/index_spanish.htm 学历:

select id,
        REPLACE(stuff((select  ,  +      + name  +      
            from @Table b
            where b.id = a.id
            FOR xml path(  )),1,1,   ), , , OR ) MergedData
from @Table a
group by a.id

<<>Output>:

id  MergedData
1      Amit  
2      Mohit  OR  Nahar

www.un.org/Depts/DGACM/index_spanish.htm 学历(关于更改要求)

select distinct a.id,

    case when coalesce(x.cnt,0) <= 2 then

        REPLACE(stuff((select  ,  +      + name  +      
            from @Table b
            where b.id = a.id
            FOR xml path(  )),1,1,   ), , , OR ) 


      when x.cnt > 2 then 

      REPLACE(stuff((select  ,  +      + name  +      
            from @Table b
            where b.id = a.id
            FOR xml path(  )),1,1,   ), , , AND ) 

            end

            MergedData
from @Table a

left join 

(select id,COUNT(id) cnt
from @Table 
group by ID
having (COUNT(id)>1))x
on a.ID = x.ID

<<>Output>:

id  MergedData
1      Amit  
2      Mohit  OR  Nahar  
3      C  AND  D  AND  E

Answer 2

你们可以尝试这样一些事情,以获得名字。

DECLARE @Table TABLE(
        ID INT,
        NAME VARCHAR(MAX)
)

DECLARE @TempTable TABLE(       
        ID INT,
        NAME VARCHAR(MAX)
)

INSERT INTO @Table (ID,[NAME]) SELECT 1,  A 
INSERT INTO @Table (ID,[NAME]) SELECT 2,  B 
INSERT INTO @Table (ID,[NAME]) SELECT 2,  C 

DECLARE @ID INT
DECLARE Cur CURSOR FOR 
SELECT  DISTINCT 
        ID
FROM    @Table

OPEN Cur
FETCH NEXT FROM Cur INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Name VARCHAR(MAX)
    SET @Name = NULL
    SELECT  @Name = COALESCE(@Name +   OR   + NAME, Name)
    FROM    @Table
    WHERE   ID = @ID
    INSERT INTO @TempTable (ID,[NAME]) SELECT @ID, @Name
    FETCH NEXT FROM Cur INTO @ID
END

CLOSE Cur
DEALLOCATE Cur

SELECT * FROM @TempTable

友情链接