T-SQL：將列轉換為行并插入/更新另一個表

尋找適合初級開發人員解決方案的非花哨、易于除錯的解決方案......在 SQL Server 2008 R2 中，我必須以所需的格式從#data一個表更新到另一個#tests表。我不確定如何使用 T-SQL 查詢歸檔結果？

注意：臨時表只有 3 列僅用于示例目的，但實際表每組有 50 多列。

這是我的表的樣子：

IF OBJECT_ID('tempdb..#tests') IS NOT NULL 
    DROP TABLE #tests
GO

CREATE TABLE #tests
(
    id int,
    FirstName varchar(100),
    LastName varchar(100),
    UniueNumber varchar(100)
)

IF OBJECT_ID('tempdb..#data') IS NOT NULL 
    DROP TABLE #data
GO

CREATE TABLE #data
(
    id int,
    FirstName1 varchar(100),
    LastName1 varchar(100),
    UniueNumber1 varchar(100),
    FirstName2 varchar(100),
    LastName2 varchar(100),
    UniueNumber2 varchar(100),
    FirstName3 varchar(100),
    LastName3 varchar(100),
    UniueNumber3 varchar(100),
    FirstName4 varchar(100),
    LastName4 varchar(100),
    UniueNumber4 varchar(100),
    FirstName5 varchar(100),
    LastName5 varchar(100),
    UniueNumber5 varchar(100),
    FirstName6 varchar(100),
    LastName6 varchar(100),
    UniueNumber6 varchar(100),
    FirstName7 varchar(100),
    LastName7 varchar(100),
    UniueNumber7 varchar(100)
)

INSERT INTO #data 
VALUES (111, 'Tom', 'M', '12345', 'Sam', 'M', '65432', 'Chris', 'PATT', '54656', 'Sean', 'Meyer', '865554', 'Mike', 'Max', '999999', 'Tee', 'itc', '656546444', 'Mickey', 'Mul', '65443231')

INSERT INTO #data  
VALUES (222, 'Kurr', 'P', '22222', 'Yammy', 'G', '33333', 'Saras', 'pi', '55555', 'Man', 'Shey', '666666', 'Max', 'Dopit', '66666678', '', '', '', '', '', '')

INSERT INTO #data 
VALUES (333, 'Mia', 'K', '625344', 'Tee', 'TE', '777766', 'david', 'mot', '4444444', 'Jeff', 'August', '5666666', 'Mylee', 'Max', '0000000', '', '', '', 'Amy', 'Marr', '55543444')

SELECT * 
FROM #data

我想將資料從#data 表插入/更新到#tests 表中。如果 #data 表中不存在 id 和 UniqueNumber 組合，則將資料插入到 #tests 表中。如果存在組合，則將資料從 #data 表更新到 #tests 表

這是#tests 表中所需的輸出

uj5u.com熱心網友回復：

一種方法是分別查詢每組列和UNION結果

SELECT 
    id int,
    FirstName1 as FirstName,
    LastName1 as LastName,
    UniueNumber1 AS SSN
FROM #data
UNION 
SELECT 
    id int,
    FirstName2 as FirstName,
    LastName2 as LastName,
    UniueNumber2 AS SSN
FROM #data
UNION 
...

沒有辦法干凈地“回圈”這 7 組列 - 您將花費更多時間構建回圈以動態創建查詢，而不僅僅是復制和粘貼查詢 6 次并更改數字。

當然，#data如果可能的話，最好避免你現在使用的那種結構。

uj5u.com熱心網友回復：

這是一個選項，可以在不使用動態 SQL 的情況下動態地 UNPIVOT 您的資料

需要明確的是： UNPIVOT 的性能會更高，但您不必列舉 50 列。

這是假設您的列以 NUMERIC 結尾，即 FirstName##

例子

Select ID
      ,FirstName
      ,LastName
      ,UniueNumber  -- You could use SSN = UniueNumber  
 From  (
        SELECT A.ID
              ,Grp
              ,Col = replace([Key],Grp,'')
              ,Value
        FROM #data A
        Cross Apply (
                        Select [Key]
                              ,Value
                              ,Grp = substring([Key],patindex('%[0-9]%',[Key]),25)
                         From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper  ) ) 
                    ) B
       ) src
 Pivot ( max(Value) for Col in ([FirstName],[LastName],[UniueNumber]) ) pvt
 Order By ID,Grp

結果

更新 XML 版本

Select ID
      ,FirstName
      ,LastName
      ,UniueNumber
 From  (
        SELECT A.ID
              ,Grp  = substring(Item,patindex('%[0-9]%',Item),50)
              ,Col  = replace(Item,substring(Item,patindex('%[0-9]%',Item),50),'')
              ,Value
        FROM #data A
        Cross Apply ( values (convert(xml,(Select A.* for XML RAW)))) B(XData)
        Cross Apply (
                        Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                              ,Value = xAttr.value('.','varchar(max)')
                         From  B.XData.nodes('//@*') xNode(xAttr)
                    ) C
        Where Item not in ('ID')
       ) src
 Pivot ( max(Value) for Col in (FirstName,LastName,UniueNumber) ) pvt
 Order By ID,Grp

標籤：

上一篇：如何從水平表中提取資料

下一篇：從具有相似列名的多個URL中決議多個表