How to use PIVOT to separate values into different columns without duplication or NULL values-CodePudding

I currently have the three tables below:

Table #Name

Name_id     NameValue
==========  =============
50          Hannah
51          Jeremy
52          Luna

Table #AttValue

Name_id  AttributeValue_id  AttributeValue
=======  =================  =============
50       11                 01216892584
50       12                 26
50       13                 Female

Table #Attribute

AttributeValue_id  AttributeName
=================  =============
11                 Phone Number
12                 Age
13                 Gender

What I want to do is combine them into one and this is what I have done so far:

SELECT NameValue,
case when AttributeName = 'Phone Number' then AttributeValue end PhoneNumber
,case when AttributeName = 'Age' then AttributeValue end Age
,case when AttributeName = 'Gender' then AttributeValue end Gender
FROM #Name A
INNER JOIN #AttValue B ON A.Name_id = B.Name_id
INNER JOIN #Attribute C ON B.AttributeValue_id = C.AttributeValue_id

This works fine however it results in multiple NameValues and NULL values which I am looking to avoid:

NameValue  PhoneNumber  Age  Gender
=========  ============  ===  ======
Hannah     01216892584   NULL NULL
Hannah     NULL          26   NULL
Hannah     NULL          NULL Female

The result I am looking for is:

NameValue  PhoneNumber  Age  Gender
=========  ===========  ===  ======
Hannah     01216892584  26   Female

I understand you can use PIVOT for this, however I struggle to figure out how to do it exactly.

CodePudding user response：

Assuming Name_id, AttributeValue_id is a key (e.g. you don't have cases where a person might have two values for PhoneNumber - or those cases exist but you don't care which one you get), the simplest way is to just wrap your CASE expressions with conditional aggregation:

SELECT 
  N.NameValue,
  PhoneNumber = MAX(case when A.AttributeName = 'Phone Number' 
                THEN AV.AttributeValue end),
  Age         = MAX(case when A.AttributeName = 'Age' 
                THEN AV.AttributeValue end),
  Gender      = MAX(case when A.AttributeName = 'Gender' 
                THEN AV.AttributeValue end)
FROM #Name AS N
INNER JOIN #AttValue AS AV ON N.Name_id = AV.Name_id
INNER JOIN #Attribute AS A ON AV.AttributeValue_id = A.AttributeValue_id
GROUP BY N.NameValue;

Or a PIVOT:

;WITH src AS
(
  SELECT N.NameValue, A.AttributeName, AV.AttributeValue
    FROM #Name AS N
    INNER JOIN #AttValue AS AV ON N.Name_id = AV.Name_id
    INNER JOIN #Attribute AS A ON AV.AttributeValue_id = A.AttributeValue_id
)
SELECT 
  p.NameValue, 
  PhoneNumber = p.[Phone Number],
  p.Age, 
  p.Gender
FROM src PIVOT (MAX(AttributeValue)
  FOR AttributeName IN ([Phone Number], [Age], [Gender])) AS p;

Example

If it helps with the visualization, the UNION ALL looks like this