合并和轉置表

我的樣本資料集如下，

| Customer |    |Detail |   |DataValues |
|----------|    |-------|   |-----------|
| ID       |    |ID     |   |CustomerID |
| Name     |    |Name   |   |DetailID   |
                            |Values |

| Customer |    |Detail   |     |DataValues |
|----------|    |---------|     |-----------|
| 1 | Jack |    | 1 | sex |     | 1 | 1 | M |
| 2 | Anne |    | 2 | age |     | 1 | 2 | 30|
                                | 2 | 1 | F |
                                | 2 | 2 | 28|

我想要的結果如下，

我沒有想出一個正確的回傳任何東西的 SQL 查詢。

提前致謝。

select Customers.Name, Details.Name, DataValues.Value from Customers
inner join DataValues on DataValues.CustomersID = Customers.ID 
inner join Details on DataValues.DetailsID = Details.ID

    

uj5u.com熱心網友回復：

靜態方式，假設您知道自己想要Sex并且Age：

WITH cte AS 
(
  SELECT c.Name, Type = d.Name, dv.[Values]
  FROM dbo.DataValues AS dv
  INNER JOIN dbo.Detail AS d
  ON dv.DetailID = d.ID
  INNER JOIN dbo.Customer AS c
  ON dv.CustomerID = c.ID
  WHERE d.Name IN (N'Sex',N'Age')
)
SELECT Name, Sex, Age
  FROM cte
  PIVOT (MAX([Values]) FOR [Type] IN ([Sex],[Age])) AS p;

如果您需要根據所有可能的屬性派生查詢，那么您將需要使用動態 SQL。這是一種方法：

DECLARE @in  nvarchar(max),
        @piv nvarchar(max),
        @sql nvarchar(max);

SELECT @in  = STRING_AGG(N'N'   QUOTENAME(Name, char(39)), ','),
       @piv = STRING_AGG(QUOTENAME(Name), ',')
  FROM (SELECT Name FROM dbo.Detail GROUP BY Name) AS src;

SET @sql = N'WITH cte AS 
(
  SELECT c.Name, Type = d.Name, dv.[Values]
  FROM dbo.DataValues AS dv
  INNER JOIN dbo.Detail AS d
  ON dv.DetailID = d.ID
  INNER JOIN dbo.Customer AS c
  ON dv.CustomerID = c.ID
  WHERE d.Name IN ('   @in   N')
)
SELECT Name, '   @piv   N'
  FROM cte
  PIVOT (MAX([Values]) FOR [Type] IN ('   @piv   N')) AS p;';

EXECUTE sys.sp_executesql @sql;

這個小提琴中的作業示例。

uj5u.com熱心網友回復：

這里有很多東西要解開。讓我們從如何呈現演示資料開始：

如果您為您的資料提供 DDL 和 DML，那么人們可以更輕松地使用：

DECLARE @Customer TABLE (ID INT, Name NVARCHAR(100))
DECLARE @Detail TABLE (ID INT, Name NVARCHAR(20))
DECLARE @DataValues TABLE (CustomerID INT, DetailID INT, [Values] NVARCHAR(20))

INSERT INTO @Customer (ID, Name) VALUES
(1, 'Jack'),(2, 'Anne')
INSERT INTO @Detail (ID, Name) VALUES
(1, 'Sex'),(2, 'Age')
INSERT INTO @DataValues (CustomerID, DetailID, [Values]) VALUES
(1, 1, 'M'),(1, 2, '30'),(2, 1, 'F'),(2, 2, '28')

這將設定您的表（作為變數）并使用演示資料填充它們。

接下來讓我們談談這里的可怕架構。您也應該始終盡量避免使用保留字作為列名。Values是一個關鍵字。這可能應該是一個客戶表：

DECLARE @Genders TABLE (ID INT IDENTITY, Name NVARCHAR(20))
DECLARE @Customer1 TABLE (CustomerID INT IDENTITY, Name NVARCHAR(100), BirthDate DATETIME, GenderID INT NULL, Age AS (DATEDIFF(YEAR, BirthDate, CURRENT_TIMESTAMP)))

請注意，我使用的是 BirthDate 而不是 Age。這是因為一個人的年齡會隨著時間而改變，但他們的出生日期不會。不應存盤基于另一個屬性計算的屬性（但如果您愿意，可以使用計算列，就像我們在這里一樣）。您還會注意到，我們將通過 Gender ID 參考它，而不是在客戶表中明確定義性別。這是一個查找表。

如果您使用了規范化模式，您的查詢將如下所示：

/* Demo Data */
DECLARE @Genders TABLE (ID INT IDENTITY, Name NVARCHAR(20));
INSERT INTO @Genders (Name) VALUES
('Male'),('Female'),('Non-Binary');
DECLARE @Customer1 TABLE (CustomerID INT IDENTITY, Name NVARCHAR(100), BirthDate DATETIME, GenderID INT NULL, Age AS (DATEDIFF(YEAR, BirthDate, CURRENT_TIMESTAMP)));
INSERT INTO @Customer1 (Name, BirthDate, GenderID) VALUES
('Jack', '2000-11-03', 1),('Anne', '2000-11-01', 2),('Chris', '2001-05-13', NULL);

/* Query */
SELECT *
  FROM @Customer1 c
    LEFT OUTER JOIN @Genders g
      ON c.GenderID = g.ID;

現在介紹如何從您擁有的結構中獲取您想要的資料。無論如何，您這樣做將是雜技，因為我們必須針對模式作業。

/* Demo Data */
DECLARE @Customer TABLE (ID INT, Name NVARCHAR(100));
DECLARE @Detail TABLE (ID INT, Name NVARCHAR(20));
DECLARE @DataValues TABLE (CustomerID INT, DetailID INT, [Values] NVARCHAR(20));

INSERT INTO @Customer (ID, Name) VALUES
(1, 'Jack'),(2, 'Anne');
INSERT INTO @Detail (ID, Name) VALUES
(1, 'Sex'),(2, 'Age');
INSERT INTO @DataValues (CustomerID, DetailID, [Values]) VALUES
(1, 1, 'M'),(1, 2, '30'),(2, 1, 'F'),(2, 2, '28');

/* Query */
SELECT *
  FROM (
        SELECT d.Name AS DetailName, c.Name AS CustomerName, DV.[Values]
         FROM @DataValues dv
           INNER JOIN @Detail d
             ON dv.DetailID = d.ID
           INNER JOIN @Customer c
             ON dv.CustomerID = c.ID
       ) a
    PIVOT (
           MAX([Values]) FOR DetailName IN (Sex,Age)
          ) p;

CustomerName    Sex Age
-----------------------
Anne            F   28
Jack            M   30

標籤：sql服务器加入合并转置

上一篇：在mssql中的字串資料中進行第二次“_”分隔后，如何獲取所有這些？

下一篇：SSRS資料驅動查詢？