[SQL] T-SQL 동적 피벗

T-SQL 동적 피벗

확인을 나는 모습이 좋아하는 테이블이

ItemID | ColumnName | Value
1      | name       | Peter
1      | phone      | 12345678
1      | email      | peter@host.com
2      | name       | John
2      | phone      | 87654321
2      | email      | john@host.com
3      | name       | Sarah
3      | phone      | 55667788
3      | email      | sarah@host.com

지금은 이것으로 저것을 설정해야합니다 :

ItemID | name  | phone    | email
1      | Peter | 12345678 | peter@host.com
2      | John  | 87654321 | john@host.com
3      | Sarah | 55667788 | sarah@host.com

나는 동적 피벗 예제를 찾고있다, 그러나 그것은 나의 시나리오로 적합 할 수 임 보인다.

누구든지 도움이 수 있습니까?

해결법

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

   1.다음 예에서보세요

   다음 예에서보세요

   CREATE TABLE #Table (
           ID INT,
           ColumnName VARCHAR(250),
           Value VARCHAR(250)
   )
   
   INSERT INTO #Table SELECT 1,'name','Peter' 
   INSERT INTO #Table SELECT 1,'phone','12345678' 
   INSERT INTO #Table SELECT 1,'email','peter@host.com' 
   INSERT INTO #Table SELECT 2,'name','John' 
   INSERT INTO #Table SELECT 2,'phone','87654321' 
   INSERT INTO #Table SELECT 2,'email','john@host.com' 
   INSERT INTO #Table SELECT 3,'name','Sarah' 
   INSERT INTO #Table SELECT 3,'phone','55667788' 
   INSERT INTO #Table SELECT 3,'email','sarah@host.com' 
   
   ---I assumed your tablename as TESTTABLE--- 
   DECLARE @cols NVARCHAR(2000) 
   DECLARE @query NVARCHAR(4000) 
   
   SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT 
                                   '],[' + t.ColumnName 
                           FROM    #Table AS t 
                           --ORDER BY '],[' + t.ID 
                           FOR XML PATH('') 
                         ), 1, 2, '') + ']' 
   
   SELECT  @cols
   
   SET @query = N'SELECT ID,'+ @cols +' FROM 
   (SELECT t1.ID,t1.ColumnName , t1.Value FROM #Table AS t1) p 
   PIVOT (MAX([Value]) FOR ColumnName IN ( '+ @cols +' )) 
   AS pvt;' 
   
   EXECUTE(@query)
   
   DROP TABLE #Table
   

2. ==============================

   2.이 시도:

   이 시도:

   SQL 서버 2005 +

    ;with 
           cte_name  as(select * from <table> where ColumnName='name'),
           cte_phone as(select * from <table> where ColumnName='phone'),
           cte_email as(select * from <table> where ColumnName='email')
     select n.ItemID,n.Value [Name],p.Value [Phone],e.Value [Email] 
     from  cte_name n
     join  cte_phone p
     on    n.ItemID=p.ItemID
     join  cte_email e
     on    n.ItemID=e.ItemID
   

3. ==============================

   3.그것은 다른 테이블 될 것입니다 때문에, 동적 피벗 필요하지 않습니다. 간단히 같은 것을 할 :

   그것은 다른 테이블 될 것입니다 때문에, 동적 피벗 필요하지 않습니다. 간단히 같은 것을 할 :

   name    phone   email
   ---------------------------------
   Peter            
           123456
                    peter@host.com
   

   이 SQL 바이올린을 확인

   SELECT DISTINCT u.ItemID, n.Value as 'name', p.Value as 'phone', e.Value as 'email'
   FROM UserData u
   INNER JOIN(
   SELECT ItemID, Value 
   FROM UserData WHERE ColumnName = 'name') n ON n.ItemID = u.ItemID
   INNER JOIN(
   SELECT ItemID, Value 
   FROM UserData WHERE ColumnName = 'phone') p ON p.ItemID = u.ItemID
   INNER JOIN(
   SELECT ItemID, Value 
   FROM UserData WHERE ColumnName = 'email') e ON e.ItemID = u.ItemID
   

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

   4.여기에 내 contactlist을 위해 사용하고 쿼리입니다 :)

   여기에 내 contactlist을 위해 사용하고 쿼리입니다 :)

   SELECT *
   FROM
       (
       SELECT Contact_Id AS CT
             , [Age]
             , [Sex]
             , [State]
             , [Country]
             , [Keyword]
             , [Married]
             , [Kids]
             , [Car]
        FROM
            (SELECT c.PropertyName
                  , c.ValueString
                  , c.Contact_Id
             FROM
                 ContactProfiles c) AS ctp
            PIVOT (max(ctp.ValueString) FOR PropertyName IN ([Age], [Sex], [State], [Country], [Keyword], [Married], [Kids], [Car])) AS PivotTable
            ) AS pvt
   
   WHERE
       pvt.[Age] > 18
       AND (pvt.[State] = 'CA' OR pvt.[State] = 'NY')
       AND pvt.[Sex] = 'F'
       --*AND pvt.[Keyword] LIKE '%B;%'
       AND pvt.[Married] = 'True'
       AND pvt.[Kids] > 0
   

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

   5.이 시도했다 :

   이 시도했다 :

   SELECT ItemID, name, phone, email
   FROM
   (SELECT [ItemID] ,[ColumnName] ,[Value] FROM Item) Item
   PIVOT (MAX(Value) FOR ColumnName IN (name, phone, email) ) as pvt 
   

from https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot by cc-by-sa and MIT license