복붙노트

[SQL] CONCATENATE 행은 T-SQL 값

SQL

CONCATENATE 행은 T-SQL 값

나는 보고서와 테이블의 하나의 행 값을 연결해야하는 몇 가지 데이터를 함께 끌어하려합니다. 여기에 기본 테이블 구조는 다음과 같습니다

리뷰

 ReviewID  
 ReviewDate

검토

 ReviewerID  
 ReviewID  
 UserID

사용자

UserID  
FName  
LName

M 관계 : 이것은 M입니다. 각각의 검토는 많은 검토를 할 수 있습니다; 각 사용자는 많은 리뷰와 연관 될 수 있습니다.

기본적으로, 내가보고 싶은 모든 Reviews.ReviewID, Reviews.ReviewDate 및 검토 (쉼표로 분리) 것을위한 FNAME의 모든 관련 사용자의의 연결된 문자열입니다.

대신에:

ReviewID---ReviewDate---User  
1----------12/1/2009----Bob  
1----------12/1/2009----Joe  
1----------12/1/2009----Frank  
2----------12/9/2009----Sue  
2----------12/9/2009----Alice

이 표시 :

ReviewID---ReviewDate----Users  
1----------12/1/2009-----Bob, Joe, Frank  
2----------12/9/2009-----Sue, Alice

나는이 작업을 수행하는 몇 가지 방법을 설명하는이 문서를 발견하지만, 이들 중 대부분은 하나 개의 테이블, 배수가 아닌 처리하는 것; 불행하게도, 내 SQL-FU 내 상황이 적응하기 위해 충분히 강한 아니다. 나는 특히이 가장 정직하고 깨끗한 모양과 같이 () XML 경로에 대한 활용이 사이트의 예에 관심이 있어요.

SELECT p1.CategoryId,
( SELECT ProductName + ', '
  FROM Northwind.dbo.Products p2
  WHERE p2.CategoryId = p1.CategoryId
  ORDER BY ProductName FOR XML PATH('')
) AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId;

그 누구도 날이와 손을 줄 수 있습니까? 어떤 도움을 주시면 감사하겠습니다!

해결법

  1. ==============================

    1.이에서보세요

    이에서보세요

    DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME
)

DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT
)

DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)

INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'

INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''

INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5

SELECT  *,
        ( 
            SELECT  u.FName + ','
            FROM    @Users u INNER JOIN 
                    @Reviewers rs ON u.UserID = rs.UserID
            WHERE   rs.ReviewID = r.ReviewID
            FOR XML PATH('')
        ) AS Products
FROM    @Reviews r
  2. ==============================

    2.UDF를 필요로하지 않는이 작업을 수행하는 더 쉬운 방법이 밝혀 :

    UDF를 필요로하지 않는이 작업을 수행하는 더 쉬운 방법이 밝혀 :

    select replace(replace(replace((cast((
        select distinct columnName as X
        from tableName 
        for xml path('')) as varchar(max))), 
   '</X><X>', ', '),'<X>', ''),'</X>','')
  3. ==============================

    3.비슷한 문제를 가지고 15 분 동안 코드와 함께 연주 후 달콤한 해결책을 발견

    비슷한 문제를 가지고 15 분 동안 코드와 함께 연주 후 달콤한 해결책을 발견

    declare @result varchar(1000)
select @result = COALESCE(@result+','+A.col1, A.col1)
                FROM (  select  col1
                        from [table] 
                ) A
select @result

    반환 값 1, 값, VALUE3, VALUE4로 결과

    즐겨 ;)

  4. ==============================

    4.SQL 서버 2017 현재 주어진 분리기를 사용하여 하나로 여러 문자열 집계 STRING_AGG있다.

    SQL 서버 2017 현재 주어진 분리기를 사용하여 하나로 여러 문자열 집계 STRING_AGG있다.

  5. ==============================

    5.3 가지 방법이 설명대로 나는 UDF 또는 3 사용 (.NET CLR로 작성)를 사용자 정의 집계 구석 구석, 커서 1. 한, 압연 된 데이터 처리했다있다. 커서 및 UDF는 매우 느리다. (행 당 약 0.1 초). CLR은 사용자 정의 집계는 놀라 울 정도로 빠릅니다. (행 당 약 0.001 초)

    3 가지 방법이 설명대로 나는 UDF 또는 3 사용 (.NET CLR로 작성)를 사용자 정의 집계 구석 구석, 커서 1. 한, 압연 된 데이터 처리했다있다. 커서 및 UDF는 매우 느리다. (행 당 약 0.1 초). CLR은 사용자 정의 집계는 놀라 울 정도로 빠릅니다. (행 당 약 0.001 초)

    당신이이 폴더의 코드를 찾을 수 있어야합니다, 그것은 설치되어있는 경우 SQL 2005에 대한 SDK의 일부로 마이크로 소프트 선박의 코드를 (당신이 원하는 것을 정확히 수행 할 작업) : C : \ 프로그램 Files \ Microsoft SQL 서버 \ 90 개 \ 샘플 \ 엔진 \ 프로그래밍 \ CLR \ StringUtilities. 또한 MSDN이 기사에 할 수 있습니다. 그것은 사용자 정의 집계를 설치하고 사용하는 방법에 대한 이야기 ​​: http://msdn.microsoft.com/en-us/library/ms161551(SQL.90).aspx

    컴파일 및 사용자 정의 집계를 설치 한 후에는 다음과 같이 조회 할 수 있어야한다 :

    SELECT Reviews.ReviewID, ReviewDate, dbo.StringUtilities.Concat(FName) AS [User]
FROM Reviews INNER JOIN Reviewers ON Reviews.ReviewID = Reviewers.ReviewID
   INNER JOIN Users ON Reviews.UserID = Users.UserID
GROUP BY ReviewID, ReviewDate;

    당신이 보여 같은과 (위) 결과 세트를 얻을 수

  6. ==============================

    6.

    select p1.Availability ,COUNT(*),
(select  name+','  from AdventureWorks2008.Production.Location p2 where 
p1.Availability=p2.Availability for XML path(''),type).value('.','varchar(max)') 
as Name  from AdventureWorks2008.Production.Location p1 group by Availability

    결과

    Availability  COUNT     Name  
---------------------------------------------------------------------------------
0.00    7   Tool Crib,Sheet Metal Racks,Paint Shop,Paint Storage,Metal 
                    Storage,Miscellaneous Storage,Finished Goods Storage,
80.00   1   Specialized Paint,
96.00   1   Frame Forming,
108.00  1   Frame Welding,
120.00  4   Debur and Polish,Paint,Subassembly,Final Assembly,
  7. ==============================

    7.이제 SQL 서버 2017에서 STRING_AGG라는 새로운 T-SQL 기능이 있습니다 : 그것은 문자열 표현과 이들 사이에 대한 세퍼레이터 값의 값을 연결 새로운 집합 함수이다. 세퍼레이터는 문자열의 끝에 부가되지 않는다.

    이제 SQL 서버 2017에서 STRING_AGG라는 새로운 T-SQL 기능이 있습니다 : 그것은 문자열 표현과 이들 사이에 대한 세퍼레이터 값의 값을 연결 새로운 집합 함수이다. 세퍼레이터는 문자열의 끝에 부가되지 않는다.

    예:

    SELECT STRING_AGG ( ISNULL(FirstName,'N/A'), ',') AS csv 
FROM Person.Person;

    결과 집합 :

    John,N/A,Mike,Peter,N/A,N/A,Alice,Bob
  8. ==============================

    8.에는, UDF는이 문제를 해결하기위한 확인 방법이 될 것입니다.

    에는, UDF는이 문제를 해결하기위한 확인 방법이 될 것입니다.

    그냥있는 int PARAM (제품 ID)를 소요되며 메소드의 이름은 GetProductNames 다음 쿼리는 다음과 같을 수있는 경우 (. 제품과 관련된 이름의 연결) 문자열을 반환하는 T-SQL 함수 (UDF)를 정의 :

    SELECT p1.CategoryId, dbo.GetProductNames(p1.CategoryId)
FROM Northwind.dbo.Products p1
GROUP BY CategoryId
  9. ==============================

    9.이 시도:

    이 시도:

     Declare @Revs Table 
 (RevId int Priimary Key Not Null,
  RevDt DateTime Null,
  users varChar(1000) default '')

 Insert @Revs (RevId, RevDt)
 Select Distinct ReviewId, ReviewDate
 From Reviews
 Declare @UId Integer
 Set @Uid = 0
 While Exists (Select * From Users
               Where UserID > @Uid)
 Begin
    Update @Revs Set
      users = users + u.fName + ', '
    From @Revs R 
       Join Reviewers uR On ur.ReviewId = R.RId
       Join users u On u.UserId = uR.UserId 
    Where uR.UserId = @UId
    Select @Uid = Min(UserId)
    From users
    Where UserId > @UId
  End
  Select * From @Revs
  10. ==============================

    10.

    Select R.ReviewID, ReviewDate
, (Select  FName + ', ' 
   from Users 
   where UserID = R.UserID 
   order by FName FOR XML PATH(')
) as [Users]
from Reviews
inner join Reviewers AS R
  On Reviews.ReviewID = R.ReviewID
Group By R.ReviewID, ReviewDate;
  11. ==============================

    11.당신이 (MySQL의에서) GROUP_CONCAT의 기능이 필요한 것처럼 보인다. 어떻게 열 (T-SQL)에서 여러 값을 반환하는 방법이 다른 시험 데이터 세트 여기를 해결하고있다?

    당신이 (MySQL의에서) GROUP_CONCAT의 기능이 필요한 것처럼 보인다. 어떻게 열 (T-SQL)에서 여러 값을 반환하는 방법이 다른 시험 데이터 세트 여기를 해결하고있다?

  12. ==============================

    12.에 데이터를 덤프하기 위해 임시 테이블을 만듭니다. 그런 다음에 대한의 XML 경로 방법을 사용합니다. 외부 쿼리는 목록 오프 마지막 쉼표를 손질하기 위해 필요합니다.

    에 데이터를 덤프하기 위해 임시 테이블을 만듭니다. 그런 다음에 대한의 XML 경로 방법을 사용합니다. 외부 쿼리는 목록 오프 마지막 쉼표를 손질하기 위해 필요합니다.

    CREATE TABLE #ReviewInfo (
ReviewId INT,
ReviewDate DATETIME,
Reviewer VARCHAR(1000))

INSERT INTO #ReviewInfo (ReviewId, ReviewDate, Reviewer)
SELECT r.ReviewId, r.ReviewDate, u.FName
FROM Reviews r
JOIN Reviewers rs ON r.ReviewId = rs.ReviewId
JOIN Users u ON u.UserId = rs.UserId

SELECT ReviewId, ReviewDate, LEFT(Users, LEN(Users)-1)
FROM (
SELECT ReviewId, ReviewDate, 
(
    SELECT Reviewer + ', '
    FROM #ReviewInfo ri2
    WHERE ri2.ReviewId = ri1.ReviewId
    ORDER BY Reviewer
    FOR XML PATH('')
) AS Users
FROM #ReviewInfo ri1
GROUP BY ReviewId, ReviewDate
) a

DROP TABLE #ReviewInfo
  13. ==============================

    13.

    select 
      p1.Availability,
      COUNT(*),
      (
          select  name+',' 
          from AdventureWorks2008.Production.Location p2 
          where p1.Availability=p2.Availability 
          for XML path(''),type
      ).value('.','varchar(max)') as Name  
 from AdventureWorks2008.Production.Location p1 
 group by Availability
  14. ==============================

    14.항목의 수가 적을 때에 의해 ROW_NUMBER () OVER 파티션을 사용하여 수행 할 수 있습니다 :

    항목의 수가 적을 때에 의해 ROW_NUMBER () OVER 파티션을 사용하여 수행 할 수 있습니다 :

    declare @t table (col1 int, col2 varchar)
insert into @t VALUES (1,'A')
insert into @t VALUES (1,'B')
insert into @t VALUES (1,'C')
insert into @t VALUES (1,'D')
insert into @t VALUES (1,'E')
insert into @t VALUES (2,'X')
insert into @t VALUES (3,'Y')

select col1,
    MAX(CASE seq WHEN 1 THEN        col2 ELSE '' END ) + 
    MAX(CASE seq WHEN 2 THEN ', ' + col2 ELSE '' END ) + 
    MAX(CASE seq WHEN 3 THEN ', ' + col2 ELSE '' END ) +
    MAX(CASE seq WHEN 4 THEN ', ' + col2 ELSE '' END ) +
    MAX(CASE seq WHEN 5 THEN ',...' ELSE '' END ) 
    as col2
from (
    select col1, col2, ROW_NUMBER() OVER ( PARTITION BY col1 ORDER BY col2 ) seq
    from @t
    group by col1, col2
) x
group by col1
  15. ==============================

    15.

    STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

    나는 SQL 서버 문자열 집계 함수를 찾고 유래에왔다.

    관련 질문은이 질문의 중복으로 표시, 폐쇄, 나는 전혀 여기 여부를 대답을 강요하고 그렇게했다.

    자세한 내용은 https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017를 참조하십시오.

    16. from https://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 두 LEFT의 GROUP_CONCAT에서 이상한 중복 동작은 GROUP_BYs의 조인  (0) 2020.03.18
[SQL] 개별 행을 모든 행을 계산 대신 계산 SQL  (0) 2020.03.18
[SQL] HOURS-00904 : 잘못된 식별자  (0) 2020.03.17
[SQL] SQL NOT IN이 작동하지 않습니다  (0) 2020.03.17
[SQL] MySQL의 결과 쉼표로 구분 된 목록으로  (0) 2020.03.17
,

티스토리툴바