SQL Server Pivoting based off of a column to be delimited

Question

I'm using SQL Server Management Studio and I have a table of survey results for project managers and I'm aggregating by question/score at the project manager RepID level:

SELECT Lower(A.RepID) as 'HHRepID' 
      ,  YEAR(A.ProjectEndDate)  AS 'Year'
      ,  MONTH(A.ProjectEndDate)  AS 'Month'
      ,  DATENAME(mm,A.ProjectEndDate) AS 'MonthName'
      , SUM(CASE WHEN A.SatisfactionWithCommunication >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeCommunicationCount' 
      , COUNT(A.SatisfactionWithCommunication) as 'CommunicationCount'
      , SUM(CASE WHEN A.InteractionConnectionWithClient >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeInteractionCount' 
      , COUNT(A.InteractionConnectionWithClient) as 'InteractionCount'
      , SUM(CASE WHEN A.OverallSatisfactionWithEngagement >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeOverallSatisfactionCount' 
      , COUNT(A.OverallSatisfactionWithEngagement) as 'OverallSatisfactionCount'
      , COUNT(A.ResponseID) as 'SurveysReturned'
      , 'SalesOps' as 'Grouping'
FROM 
   SurveyData.dbo.SalesSurvey as A with(nolock) 
WHERE 
   A.ResponseID IS NOT NULL AND A.IsExcludedFromReporting IS NULL
GROUP BY
   YEAR(A.ProjectEndDate), MONTH(A.ProjectEndDate), DATENAME(mm,A.ProjectEndDate), A.RepID
ORDER BY 
   A.RepID

The output would look something like this:

enter image description here

Everything is great. Here's the problem. For each response for a project manager, there could be multiple Project Assistants. The project assistants for each project are aggregated (separated by ;) in one column:

enter image description here

What I need to do is pivot/delimit these results so each projectassistantID will be 1 row with the same grouped data as if it was a project manager. So for example, let's say that that row from the first screenshot had (HHRepID = jdoe) had 2 project assistants to it (call them Michael Matthews and Sarah Boyd): mmathews; sboyd. Via pivot/delimit, the output of the 2nd query would look like this:

enter image description here

In the actual table, it's just 1 record. But b/c there're multiple names in the ProjectAssistantID column, I need to pivot/delimit those out and essentially get the same results for each instance, just with ProjectAssistants rather than Project Managers.

I've been able to find some stuff on pivoting but this is pivoting based on delimiting values which adds an extra layer of complexity. It's entirely possible that there could be only 1 project assistant per project or as many as 6.

I'm sure that there is someone here that can help you with an amazing solution. But you should seriously consider a schema change. I can think of no good reason to have delimited values in a database field. — crthompson, Mar 19 '14 at 21:35

score 1 · Accepted Answer · edited May 23 '17 at 11:45

You can find several string split functions when you google for that, I will just assume this one which returns a table with one column named Item.

Then you can use cross apply as follows:

SELECT assist.Item as 'HHRepID' 
      ,  YEAR(A.ProjectEndDate)  AS 'Year'
      ,  MONTH(A.ProjectEndDate)  AS 'Month'
      ,  DATENAME(mm,A.ProjectEndDate) AS 'MonthName'
      , SUM(CASE WHEN A.SatisfactionWithCommunication >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeCommunicationCount' 
      , COUNT(A.SatisfactionWithCommunication) as 'CommunicationCount'
      , SUM(CASE WHEN A.InteractionConnectionWithClient >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeInteractionCount' 
      , COUNT(A.InteractionConnectionWithClient) as 'InteractionCount'
      , SUM(CASE WHEN A.OverallSatisfactionWithEngagement >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeOverallSatisfactionCount' 
      , COUNT(A.OverallSatisfactionWithEngagement) as 'OverallSatisfactionCount'
      , COUNT(A.ResponseID) as 'SurveysReturned'
      , 'SalesOps' as 'Grouping'
FROM 
   SurveyData.dbo.SalesSurvey as A with(nolock) 
   CROSS APPLY Split(Lower(A.RepID), ';') AS assist
WHERE 
   A.ResponseID IS NOT NULL AND A.IsExcludedFromReporting IS NULL
GROUP BY
   YEAR(A.ProjectEndDate), MONTH(A.ProjectEndDate), DATENAME(mm,A.ProjectEndDate), assist.Item
ORDER BY 
   assist.Item

SoulTrain · Answer 2 · 2014-03-20T00:17:42.627

For a strictly sql solution, you could use recursive cte to get your names split and then join it back to you query..

Try the below query

DECLARE @col varchar(20)='a;b;c;g;y;u;i;o;p;';
WITH CTE as
(
SELECT SUBSTRING(@COL,CHARINDEX(';',@COL)+1, LEN(@COL)-CHARINDEX(';', @COL)) col
,SUBSTRING(@COL,0, CHARINDEX(';',@COL)) Split_Names
, 1 i

union ALL 
SELECT SUBSTRING(COL,CHARINDEX(';',COL)+1, LEN(COL)-CHARINDEX(';', COL)) col
,SUBSTRING(COL,0, CHARINDEX(';',COL)) Split_Names
, i+1
from CTE

WHERE CHARINDEX(';', col)>1
)
SELECT * FROM CTE

You will need to use your column in place of @col and change the code to refer to your table in the first union statement.

Hope this helps...

So you would do something like this...

    WITH ProjectAssistants as
        (
        SELECT SUBSTRING(PROJECTASSISTANTID,CHARINDEX(';',PROJECTASSISTANTID)+1, LEN(PROJECTASSISTANTID)-CHARINDEX(';', PROJECTASSISTANTID)) col
        ,SUBSTRING(PROJECTASSISTANTID,0, CHARINDEX(';',PROJECTASSISTANTID)) Assistnames_Names
        , ProjectManagerID
        FROM YOUR_PROJECT_ASSISTANT_TABLE_NAME

        union ALL 
        SELECT SUBSTRING(COL,CHARINDEX(';',COL)+1, LEN(COL)-CHARINDEX(';', COL)) col
        ,SUBSTRING(COL,0, CHARINDEX(';',COL)) Assistnames_Names
        , ProjectManagerID
        from ProjectAssistants 

        WHERE CHARINDEX(';', col)>1
        )
        SELECT Lower(A.RepID) as 'HHRepID' 
      ,  YEAR(A.ProjectEndDate)  AS 'Year'
      ,  MONTH(A.ProjectEndDate)  AS 'Month'
      ,  DATENAME(mm,A.ProjectEndDate) AS 'MonthName'
      , SUM(CASE WHEN A.SatisfactionWithCommunication >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeCommunicationCount' 
      , COUNT(A.SatisfactionWithCommunication) as 'CommunicationCount'
      , SUM(CASE WHEN A.InteractionConnectionWithClient >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeInteractionCount' 
      , COUNT(A.InteractionConnectionWithClient) as 'InteractionCount'
      , SUM(CASE WHEN A.OverallSatisfactionWithEngagement >= 4 THEN 1 ELSE 0 END) as 'AgreeStronglyAgreeOverallSatisfactionCount' 
      , COUNT(A.OverallSatisfactionWithEngagement) as 'OverallSatisfactionCount'
      , COUNT(A.ResponseID) as 'SurveysReturned'
      , 'SalesOps' as 'Grouping'
FROM 
   SurveyData.dbo.SalesSurvey as A with(nolock) 
LEFT JOIN ProjectAssistants as B
   ON A.ProjectManagerID=B.ProjectManagerID
WHERE 
   A.ResponseID IS NOT NULL AND A.IsExcludedFromReporting IS NULL
GROUP BY
   YEAR(A.ProjectEndDate), MONTH(A.ProjectEndDate), DATENAME(mm,A.ProjectEndDate), A.RepID
ORDER BY 
   A.RepID

Assumptions:

Your Surveydata table has a ProjectManagerid column
Left Join-ed the ProjectManager CTE considering you still want to show data when there arent any assistants.

Thanks. I'll try to implement both solutions today and see where I get with them, then report back. — Joshua, Mar 20 '14 at 12:14

SQL Server Pivoting based off of a column to be delimited

2 Answers2