SQL 2014 - amalgamating rows into single row, multiple columns with CASE statements

Question

SQL Server 2014 using T-SQL.

In our database, customers have issues(!) and each issue assigned against a customer has a separate row in the table I'm querying.

I'm looking to create an single row for each of these issues (along with some other information) but at the moment, each of my CASE statements is creating a single row.

So the output is current like this (I've changed some header names so they fit on here):

Prod    Date        Time    Cust    Lname   Fname   Street 1          S2    City            Zip     Email               CSI     Action  Dog Prog    TourHeadsets
Tosca   08-Apr-17   2:30 PM 122253  Smith   Michael 33 Rodeo Drive    NULL  Beverley Hills  90210   msmith@email.com    NULL    NULL    0   None    0   1
Tosca   08-Apr-17   2:30 PM 122253  Smith   Michael 33 Rodeo Drive    NULL  Beverley Hills  90210   msmith@email.com    NULL    NULL    0   Large   0   0
Tosca   08-Apr-17   2:30 PM 122253  Smith   Michael 33 Rodeo Drive    NULL  Beverley Hills  90210   msmith@email.com    NULL    NULL    0   None    2   0
Tosca   08-Apr-17   2:30 PM 125634  Brown   Sarah   22 Victory Drive  NULL  Beverley Hills  90210   sbrown@email.com    NULL    NULL    0   Large   0   0
Tosca   08-Apr-17   2:30 PM 125634  Brown   Sarah   22 Victory Drive  NULL  Beverley Hills  90210   sbrown@email.com    NULL    NULL    0   None    2   0
Tosca   08-Apr-17   2:30 PM 125634  Brown   Sarah   22 Victory Drive  NULL  Beverley Hills  90210   sbrown@email.com    NULL    NULL    0   None    0   2

But I'd like this (with the result of the 4 CASE statements in separate columns in the same row):

Prod Date       Time    Cust    Lname   Fname   Street 1         S2  City   Zip Email   CSI Action  Dog Prog    Tour    Headsets
Tosca   08-Apr-17   2:30 PM 122253  Smith   Michael 33 Rodeo Drive   NULL   Beverley Hills  90210   msmith@email.com    NULL    NULL    0   Large print 2   1
Tosca   08-Apr-17   2:30 PM 125634  Brown   Sarah   22 Victory Drive NULL   Beverley Hills  90210   sbrown@email.com    NULL    NULL    0   Large print 2   2

Any help for a rookie?! (Any other feedback on the code very welcome too. I'm totally new to this.) Here's the code I've been using to get this far:

USE impresario
    SELECT 
    g.description AS 'Production'
    ,CONVERT(varchar,f.perf_dt,106) AS 'Date'
    ,FORMAT(CAST(f.perf_dt AS DATETIME),'h:mm tt') AS 'Time'
    ,a.customer_no AS 'Customer'
    ,b.lname AS 'Last name'
    ,b.fname AS 'First name'
    ,c.street1 AS 'Street 1'
    ,c.street2 AS 'Street 2'
    ,c.city AS 'City'
    ,c.postal_code AS 'Postal code'
    ,d.address
    ,a.notes AS 'CSI notes' 
    ,e.notes AS 'Action notes'
    ,CASE h.id
       WHEN 14 THEN '1'
       WHEN 15 THEN '2'
       ELSE '0'
       END
       AS 'Dogs'
    ,CASE h.id
       WHEN 16 THEN 'Large print'
       WHEN 17 THEN 'Braille'
       ELSE 'None'
       END
       AS 'Programmes'
    ,CASE h.id
       WHEN 18 THEN '1'
       WHEN 19 THEN '2'
       ELSE '0'
       END
       AS 'Touch tour'
    ,CASE h.id
       WHEN 20 THEN '1'
       WHEN 21 THEN '2'
       ELSE '0'
       END
       AS 'Headsets'
      FROM T_CUST_ACTIVITY a
      JOIN T_CUSTOMER b ON b.customer_no=a.customer_no
      JOIN T_ADDRESS c ON c.customer_no=a.customer_no
      JOIN T_EADDRESS d ON d.customer_no=a.customer_no
      JOIN T_ISSUE_ACTION e ON e.activity_no=a.activity_no
      JOIN T_PERF f ON f.perf_no=a.perf_no
      JOIN T_INVENTORY g ON g.inv_no=f.prod_season_no
      JOIN TR_ACTION h ON h.id=e.action
      WHERE a.activity_type=21 --'Access requirements' from TR_CUST_ACTIVITY_TYPE
      AND c.primary_ind='Y' --Primary addresses only
      AND d.primary_ind='Y' --Primary emails only
      AND e.action IN 
      (
       14 --Dog x1
      ,15 --Dog x2
      ,16 --Programme (large print)
      ,17 --Programme (braille)
      ,18 --Touch tour x1
      ,19 --Touch tour x2
      ,20 --Headset x1
      ,21 --Headset x2
      )
      ORDER BY f.perf_dt, a.customer_no ASC

It looks like maybe you have multiple rows being returned from your `TR_ACTION` table. Check your data and see which rows are being duplicated. Also, I prefer having my JOIN conditions in the actual JOIN instead of the where. ie `JOIN T_ADDRESS c ON c.customer_no = a.customer_no AND c.primary_ind='y'`. Having a condition outside of the JOIN will join tableA to tableB on your ON condition, and then remove rows based on your WHERE. This makes the joined data larger and can sometimes result in unintended rows being returned, especially in OUTER JOINs. It helps to think of your data in sets. — Shawn, Jul 11 '17 at 16:40
That's really helpful about the JOIN - thank you. It also looks visually less confusing. Like you said, thinking of it in sets definitely helps. With regards to the multiple rows, the table does have a row for each action/issue, which I'm guessing is why they're coming out as separate lines in the query. Ideally, I'd like to do what in a rookie way I'd describe as an Excel pivot to amalgamate those rows with the common denominator (the customer), then each action/issue in a different cell to its right. — Impresario, Jul 11 '17 at 17:01
The update of the query depends on lots of condition here on what you want to do in different scenario. What do you want to show when when Michael smith have another row(4th row) with prog = braille, dog = 1, touch tour = 1 and headset = 2? Do you want to amalgamate into 2 rows? what is the selection criteria here? — CuriousKid, Jul 11 '17 at 17:21
With a pivot, you kinda have to know what you want your final structure to look like. But as CuriousKid said, what if you have more than 2 rows returned? If you only want 1 of the results from the subquery, you can use a ROW_NUMBER() window function or a TOP 1 or something along those lines to ensure you only return one row per base record. — Shawn, Jul 11 '17 at 18:56
Thanks for the input. I'm looking to amalgamate any data returned on the current CASE scenarios into 1 single row per customer. So it doesn't matter what the combination of dogs/headsets/tours/programmes is per customer; only that they get included in the dogs/headsets/tours/programmes cell on the amalgamated line, if that makes sense. — Impresario, Jul 17 '17 at 11:08

Hogan · Answer 1 · 2017-07-11T18:15:38.600

I do something like this when faced with this kind of problem -- use a sub-query or CTE to group the items you want to combine. It is hard to know for sure what you need exactly without more detail on your data model and edge cases. (@CuriousKid gives some hints about these cases in his comments.) But I made assumptions about the most common used cases.

Note, I took out the un-needed join and cleaned up some of the horrible formatting.

 WITH actions AS
 (
    SELECT
      activity_no,
      MAX(CASE e.action WHEN 14 THEN '1' WHEN 15 THEN '2' ELSE null END) AS dogs,
      MAX(CASE e.action WHEN 16 THEN 'Large print' WHEN 17 THEN 'Braille' ELSE null END) AS programmes,
      MAX(CASE e.action WHEN 20 THEN '1'  WHEN 21 THEN '2' ELSE null END AS headsets
      MAX(CASE e.action WHEN 18 THEN '1' WHEN 19 THEN '2' ELSE null END) AS tt,
      LISTAGG(e.notes, ', ') as notes
    FROM T_ISSUE_ACTION e
    WHERE e.action IN (
       14 --Dog x1
      ,15 --Dog x2
      ,16 --Programme (large print)
      ,17 --Programme (braille)
      ,18 --Touch tour x1
      ,19 --Touch tour x2
      ,20 --Headset x1
      ,21 --Headset x2
    )
    GROUP BY activity_no
 )
 SELECT 
    g.description AS 'Production'
    ,CONVERT(varchar,f.perf_dt,106) AS 'Date'
    ,FORMAT(CAST(f.perf_dt AS DATETIME),'h:mm tt') AS 'Time'
    ,a.customer_no AS 'Customer'
    ,b.lname AS 'Last name'
    ,b.fname AS 'First name'
    ,c.street1 AS 'Street 1'
    ,c.street2 AS 'Street 2'
    ,c.city AS 'City'
    ,c.postal_code AS 'Postal code'
    ,d.address
    ,a.notes AS 'CSI notes' 
    ,e.notes AS 'Action notes'
    ,e.dogs AS 'Dogs'
    ,e.programmes AS 'Programmes'
    ,e.tt AS 'Touch tour'
    ,e.headset AS 'Headsets'
 FROM T_CUST_ACTIVITY a
 JOIN T_CUSTOMER b ON b.customer_no=a.customer_no
 JOIN T_ADDRESS c ON c.customer_no=a.customer_no  AND c.primary_ind='Y' --Primary addresses only
 JOIN T_EADDRESS d ON d.customer_no=a.customer_no   AND d.primary_ind='Y' --Primary emails only
 LEFT JOIN actions e ON e.activity_no=a.activity_no
 JOIN T_PERF f ON f.perf_no=a.perf_no
 JOIN T_INVENTORY g ON g.inv_no=f.prod_season_no
 WHERE a.activity_type=21 --'Access requirements' from TR_CUST_ACTIVITY_TYPE
 ORDER BY f.perf_dt, a.customer_no ASC

Thanks for your input (and the tidying - sorry, rookie here!). LISTAGG is throwing up an error as an unknown function in SQL Server 2014. Is there any alternative that might work? — Impresario, Jul 17 '17 at 11:10
Good point -- STRING_AGG will be in a future version of SQL Server. Here is (IMHO) the best article on alternatives on SQL Server. https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation — Hogan, Jul 17 '17 at 15:41

score 0 · Answer 2 · answered Jul 11 '17 at 17:58

0

You can use your query in a cte and use row_number to get this results as below:

;With Cte as (
    --your full query
), Cte2 as (
Select *, RowN = Row_Number() over(partition by Lname, Fname order by [Tour] desc)
    , [HeadSetsMax] = max(headsets) over(Partition by Lname, Fname)
    from Cte
    )
    Select * from Cte2 where RowN = 1

answered Jul 11 '17 at 17:58

Kannan Kandasamy

13,405
3
25
38

why not just use a group by? seems extra complex without adding value. – Hogan Jul 11 '17 at 18:14
It purely on the requirement, What happens for a same person if he has two records with different address, In this scenario Row_Number will not duplicate the records whereas group by will duplicate. So it depends on OP's requirement – Kannan Kandasamy Jul 11 '17 at 18:20
Yep to avoid two rows for single person I used row_number approach instead of group by... – Kannan Kandasamy Jul 11 '17 at 18:31
I miss read your comment -- how exactly would a group by duplicate? – Hogan Jul 11 '17 at 18:32

score 0 · Accepted Answer · answered Jul 17 '17 at 11:31

I found a way to solve this using MAX against each of the CASE statements, which appears to have worked:

USE impresario
SELECT 
g.description AS 'Production'
,CONVERT(varchar,f.perf_dt,106) AS 'Date'
,FORMAT(CAST(f.perf_dt AS DATETIME),'h:mm tt') AS 'Time'
,a.customer_no AS 'Customer'
,b.lname AS 'Last name'
,b.fname AS 'First name'
,c.street1 AS 'Street 1'
,c.street2 AS 'Street 2'
,c.city AS 'City'
,c.postal_code AS 'Postal code'
,d.address
,a.notes AS 'CSI notes' 
,e.notes AS 'Action notes'
,MAX(CASE h.id
   WHEN 14 THEN '1'
   WHEN 15 THEN '2'
   ELSE '0'
   END)
   AS 'Dogs'
,MAX(CASE h.id
   WHEN 16 THEN 'Large print'
   WHEN 17 THEN 'Braille'
   ELSE 'None'
   END)
   AS 'Programmes'
,MAX(CASE h.id
   WHEN 18 THEN '1'
   WHEN 19 THEN '2'
   ELSE '0'
   END)
   AS 'Touch tour'
,MAX(CASE h.id
   WHEN 20 THEN '1'
   WHEN 21 THEN '2'
   ELSE '0'
   END)
   AS 'Headsets'
  FROM T_CUST_ACTIVITY a
  JOIN T_CUSTOMER b ON b.customer_no=a.customer_no
  JOIN T_ADDRESS c ON c.customer_no=a.customer_no
  JOIN T_EADDRESS d ON d.customer_no=a.customer_no
  JOIN T_ISSUE_ACTION e ON e.activity_no=a.activity_no
  JOIN T_PERF f ON f.perf_no=a.perf_no
  JOIN T_INVENTORY g ON g.inv_no=f.prod_season_no
  JOIN TR_ACTION h ON h.id=e.action
  WHERE a.activity_type=21 --'Access requirements' from TR_CUST_ACTIVITY_TYPE
  AND c.primary_ind='Y' --Primary addresses only
  AND d.primary_ind='Y' --Primary emails only
  AND e.action IN 
  (
   14 --Dog x1
  ,15 --Dog x2
  ,16 --Programme (large print)
  ,17 --Programme (braille)
  ,18 --Touch tour x1
  ,19 --Touch tour x2
  ,20 --Headset x1
  ,21 --Headset x2
  )
  GROUP BY 
  g.description
  , f.perf_dt
  , a.customer_no
  , b.lname
  , b.fname
  , c.street1
  , c.street2
  , c.city
  , c.postal_code
  , d.address
  , a.notes
  , e.notes
  ORDER BY 
  f.perf_dt
  , a.customer_no ASC

SQL 2014 - amalgamating rows into single row, multiple columns with CASE statements

3 Answers3