Combining records from query results

Question

In my query below, the data in customfielddata.fielddata is saved independently based on the value of customfielddefinitions.customfieldname.

For example:

Table CustomFieldDefinitions:

CustomFieldID  CustomFieldName
------------------------------
1234           Business Owner
5678           Client
9012           Cost Center

Table CustomFieldData:

CustomFieldID  FieldData    Updated
--------------------------------------
1234           barb         1/1/2018
5678           health plan  1/1/2018
9012           68121        1/1/2018

I want to be able to pull 1 record that has all FieldData values combined. However, the query is returning NULL for the CustomFieldIDs that don't apply to the CASE statements.

For example:

IssueID  Business_Owner  Client       Cost_Center
-------------------------------------------------
176367   NULL            NULL         68121
176367   Barb S          NULL         NULL
176367   NULL            Health Plan  NULL

I understand why this is happening, but I'm not sure of a solution. I am using SSMS. Any help for a beginner is appreciated, thank you!

 SELECT 
     GI.issueid,
     CASE 
        WHEN GCD.customfieldname = 'Business Owner'
            THEN GC1.fielddata
    END AS Business_Owner,
    CASE 
        WHEN GCD.customfieldname = 'Client'
            THEN GC1.fielddata
    END AS Client,
    CASE 
        WHEN GCD.customfieldname = 'Cost Center'
            THEN GC1.fielddata
    END AS Cost_Center,
    GI.closeddate AS Closed
FROM
    dbo.gemini_issues GI
INNER JOIN 
    (SELECT 
         MAX(created) AS Created,
         fielddata, issueid, customfieldid
     FROM 
         gemini_customfielddata
     GROUP BY 
         fielddata, issueid, customfieldid) GC1 ON GI.issueid = GC1.issueid
INNER JOIN 
    dbo.gemini_customfielddefinitions GCD ON GC1.customfieldid = GCD.customfieldid 
                                          AND GCD.customfieldname IN ('cost center', 'business owner', 'client')
WHERE 
    GI.projectid IN (193, 194, 195)
ORDER BY 
    issueid

Answer 1

Using the PIVOT operator to rotate rows to columns is the best way to deal with this and in most cases result in better performance also. See sample below:

-- Sample data
CREATE TABLE Issues (
    IssueID int NOT NULL,
    CustomFieldID int NOT NULL,
    FieldData nvarchar(max) NOT NULL,
    Created datetime default getutcdate()
)

INSERT Issues (IssueID, CustomFieldID, FieldData)
VALUES
(176367, 1234, 'barb'),
(176367, 5678, 'health plan'),
(176367, 9012, '68121'),
(176368, 1234, 'don'),
(176368, 5678, 'health plan2'),
(176368, 9012, '12345')


CREATE TABLE CustomFieldDefinitions (
    CustomFieldID int NOT NULL PRIMARY KEY,
    CustomFieldName nvarchar(max) NOT NULL)

INSERT CustomFieldDefinitions VALUES
(1234, 'Business Owner'),
(5678, 'Client'),
(9012, 'Cost Center')


-- Query
SELECT IssueID, [Business Owner], [Client], [Cost Center]
FROM
(
   SELECT i.IssueID, c.CustomFieldName, i.FieldData
   FROM Issues i
   INNER JOIN CustomFieldDefinitions c ON i.CustomFieldID = c.CustomFieldID
   -- add other conditions to filter Issues
) AS SourceTable
PIVOT
(
   MAX(FieldData)
   FOR CustomFieldName IN ([Business Owner], [Client], [Cost Center])
) AS PivotTable;

-- Results
| IssueID | Business Owner |       Client | Cost Center |
|---------|----------------|--------------|-------------|
|  176367 |           barb |  health plan |       68121 |
|  176368 |            don | health plan2 |       12345 |

See this sql fiddle for working example: http://sqlfiddle.com/#!18/5cd01/4

Edit: I combined Issues and CustomFieldData into a single Issues table in the sample. In your case, you would just need to do an additional join in the inner query (SourceTable)

Answer 2

I think you just need a max here:

 SELECT GI.issueid
    ,max(CASE 
        WHEN GCD.customfieldname = 'Business Owner'
            THEN GC1.fielddata
        END) AS Business_Owner
    ,max(CASE 
        WHEN GCD.customfieldname = 'Client'
            THEN GC1.fielddata
        END) AS Client
    ,max(CASE 
        WHEN GCD.customfieldname = 'Cost Center'
            THEN GC1.fielddata
        END) AS Cost_Center
    ,GI.closeddate AS Closed
FROM dbo.gemini_issues GI
INNER JOIN (
    SELECT max(created) AS Created
        ,fielddata
        ,issueid
        ,customfieldid
    FROM gemini_customfielddata
    GROUP BY fielddata
        ,issueid
        ,customfieldid
    ) GC1 ON GI.issueid = GC1.issueid
INNER JOIN dbo.gemini_customfielddefinitions GCD ON GC1.customfieldid = GCD.customfieldid AND GCD.customfieldname IN ('cost center', 'business owner', 'client')
WHERE GI.projectid IN (193, 194, 195)
group by GI.issueid
ORDER BY GI.issueid