merging and transposing tables

Question

I have sample data set as follows,

| 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|

and my desired outcome is below,

Name	Sex	Age
Jack	M	30
Anne	F	28

I have failed to come up with a correct SQL Query that returns anything.

Thanks in advance.

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

    

Answer 1

The static way, assuming you know you want exactly Sex and 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;

If you need to derive the query based on all of the possible attributes, then you'll need to use dynamic SQL. Here's one way:

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;

Working examples in this fiddle.

Answer 2

There's a lot to unpack here. Let's start with how to present demo data:

If you provide the DDL and DML for your data it makes it much easier for folks to work with:

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')

This sets up your tables (as variables) and populates them with the demo data.

Next let's talk about the horrible schema here. You should always to try avoid reserved words as column names too. Values is a keyword. This should probably be a single customers table:

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)))

Notice I used BirthDate instead of Age. This is because a persons age will change over time, but their birth date will not. Attributes that are calculated based on another attribute shouldn't be stored (but if you want you can used a calculated column, as we are here). You'll also note that instead of explicitly defining gender in the customers table we instead will reference it by Gender ID. This is a lookup table.

If you had used a normalized schema your query would then look like:

/* 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;

Now on to how to get the data you want from the structure you have. Anyway you do this is going to be acrobatic because we have to work against the schema.

/* 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