Combine similar (but not same) data from column into single row

Question

I am a beginner with SQL Server and am struggling to figure out how to update my query so that it displays my data correctly.

I checked out Combine similar column data from two rows into one row and attempted to do a group by on QueryID but am not sure how to apply this when I'm needing it to group by the number at the end of the text and by the id#. I have also looked at numerous posts on pivot/unpivot to perform multiple pivots but am struggling to apply it.

My current query and sample results are below. What I'm wanting to see is where the QueryID ends in the same number AND the employee ID is equal, the Date/Level/Reason all return to the same row rather than 3 separate rows. I appreciate any guidance.

With DisciplineTable as
(
   Select HrEmployees.Number, 
     HrEmployees.FirstName + ' ' + HrEmployees.LastName as Name,    
     HrEmployeeQueries.QueryID, 
     HrEmployeeQueries.Query, 
     HrEmployeeQueries.Response
    From HrEmployeeQueries
    Left Join HrEmployees on HrEmployees.EmployeeID = HrEmployeeQueries.EmployeeID
    Where QueryID Like 'DIS%')
Select * 
From DisciplineTable
Pivot(Max(Response) for Query in ([DATE:],[LEVEL:],[REASON:])) as DisciplinaryAction
Where QueryID Like 'DIS%' 
Order by Name

My results look something like this:

+-----+----------+---------+----------+---------+-------------+
|  #  |   Name   | QueryID |  DATE:   | LEVEL:  |   REASON:   |
+-----+----------+---------+----------+---------+-------------+
| 123 | John Doe | Date2   | 10/16/14 | Null    | Null        |
| 123 | John Doe | Level2  | Null     | Serious | Null        |
| 123 | John Doe | Reason2 | Null     | Null    | Attendance  |
| 123 | John Doe | Date3   | 12/13/14 | Null    | Null        |
| 123 | John Doe | Level3  | Null     | Major   | Null        |
| 123 | John Doe | Reason3 | Null     | Null    | Performance |
+-----+----------+---------+----------+---------+-------------+

and I'm trying to get them to look like this. The Action column should be populated with the numeric value at the end of QueryID, which could go from 1-10. The text at the start of QueryID will always be Date, Reason or Level:

+-----+----------+--------+----------+---------+-------------+
|  #  |   Name   | Action |  DATE:   | LEVEL:  |   REASON:   |
+-----+----------+--------+----------+---------+-------------+
| 123 | John Doe |      2 | 10/16/14 | Serious | Attendance  |
| 123 | John Doe |      3 | 12/13/14 | Major   | Performance |
+-----+----------+--------+----------+---------+-------------+

What is `Action`? It looks like you need to remove the `QueryID` columns from your subquery and replace it with the `Action` column. — Taryn, Dec 16 '14 at 17:24
@ bluefeet Action would just be the number at the end of QueryID - so Date2, Level2, and Reason 2 would just display as 2 in an Action column. — HRIS, Dec 16 '14 at 17:35
@HRIS Is action only ever a single numeric value like `2` or `3`? Or will you have `10`, `100`? — Taryn, Dec 16 '14 at 17:40
@ NoDisplayName - the DisciplineTable results before the pivot are basically this: 123 | John Doe | Date2 | DATE: | 10/16/14, so the pivot moved DATE, LEVEL, and REASON up as column headers over their 'response.' — HRIS, Dec 16 '14 at 17:41
@HRIS Are your names always `Date`, `Level` and `Reason` or will those change? — Taryn, Dec 16 '14 at 17:43

score 3 · Answer 1 · edited May 23 '17 at 10:26

A few suggestions to fix this. Part of the problem is you have multiple values in the QueryID column so the PIVOT function is grouping on each of those value which generates different rows.

First, to get the Action values you'll need to strip out the alpha characters in the QueryID column. You can utilize STUFF and PATINDEX to get this considering you are going to have values from 1-10. Note: there are several ways to do this including using replace, etc. Once you have the Action values, then you apply the PIVOT. Here is a sample version:

select Number, Name, Action, [DATE:],[LEVEL:],[REASON:]
from
(
  select number, name, reason, query,
    Action = stuff(QueryID, 1, patindex('%[0-9]%', QueryID)-1, '')
  from yourdata
) d
pivot
(
  max(reason)
  for query in ([DATE:],[LEVEL:],[REASON:])
) p;

See SQL Fiddle with Demo. Then adding this to your current query it would be:

With DisciplineTable as
(
    Select 
        HrEmployees.Number, 
        HrEmployees.FirstName + ' ' + HrEmployees.LastName as Name, 
        Action = stuff(HrEmployeeQueries.QueryID, 1, patindex('%[0-9]%', HrEmployeeQueries.QueryID)-1, '') , 
        HrEmployeeQueries.Query, 
        HrEmployeeQueries.Response
    From HrEmployeeQueries
    Left Join HrEmployees on HrEmployees.EmployeeID = HrEmployeeQueries.EmployeeID
    Where QueryID Like 'DIS%'
)
Select Number, Name, Action, [DATE:],[LEVEL:],[REASON:]
From DisciplineTable
Pivot
(
    Max(Response) for Query in ([DATE:],[LEVEL:],[REASON:])
) as DisciplinaryAction
Order by Name

Pரதீப் · Accepted Answer · 2014-12-16T18:22:03.943

0

Just add Max aggregate with group by

WITH DisciplineTable
     AS (SELECT HrEmployees.Number,
                HrEmployees.FirstName + ' '
                + HrEmployees.LastName AS NAME,
                substring(HrEmployeeQueries.QueryID,patindex('%[0-9]%',HrEmployeeQueries.QueryID),LEN(HrEmployeeQueries.QueryID))QueryID,
                HrEmployeeQueries.Query,
                HrEmployeeQueries.Response
         FROM   HrEmployeeQueries
                LEFT JOIN HrEmployees
                       ON HrEmployees.EmployeeID = HrEmployeeQueries.EmployeeID
         WHERE  QueryID LIKE 'DIS%')
SELECT [#],
       [Name],
       [QueryID] Action,
       [DATE:],
       [LEVEL:],
       [REASON:]
FROM   DisciplineTable
       PIVOT(Max(Response)
            FOR Query IN ([DATE:],
                          [LEVEL:],
                          [REASON:])) AS DisciplinaryAction
ORDER  BY NAME

edited Dec 16 '14 at 18:22

answered Dec 16 '14 at 17:50

Pரதீப்

91,748
19
131
172

Not my DV but a few comments - First, you probably don't need the `WHERE` on the outer query - everything should be filtered in the inner query. Second, why not trim the `QueryID` in the inner query so you don't have to apply an aggregate function twice? Third, they stated that the `QueryID` can go up to 10 so getting the last character won't work. – Taryn Dec 16 '14 at 17:54
@bluefeet - Yep i didn't see that where clause in outer query!! thanks for pointing out the mistakes – Pரதீப் Dec 16 '14 at 17:58
@NoDisplayName The query worked prior to the update to handle queryid (possibility of 2 digits) with the only issue being that 10 displayed as 0. After the update, I'm getting the following error: Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'HrEmployeeQueries'. – HRIS Dec 16 '14 at 18:19
@NoDisplayName Disregard - added a comma and the error went away – HRIS Dec 16 '14 at 18:21

Combine similar (but not same) data from column into single row

2 Answers2