Merge multiple rows into one

Question

I'm using SQL server 2012. My table looks like

The different visit types can be - Completed,Bumped,No Show, Late Cancelled and Cancelled. I want to have my output as

Thanks for your help.

Paul, do you want to retrieve this data to use where? C# linq has a trick to group exactly like you want to. — Daniel Moreira, May 21 '18 at 13:58
SQL is not suitable for formatting data sets. It would be a really bad idea to handle this kind of operation at DB-Server level. — Giorgos Betsos, May 21 '18 at 13:59
*Merging cells* is a concern of grids and reports, not SQL. Almost all grid controls and libraries can merge adjacent rows or columns with identical values. Where are you trying to display this data? — Panagiotis Kanavos, May 21 '18 at 14:01
Possible duplicate of [GridView with merged cells](https://stackoverflow.com/questions/16147963/gridview-with-merged-cells) — Panagiotis Kanavos, May 21 '18 at 14:02
Even if the grid you use doesn't support merging, you can replicate it by removing borders and text, [as shown here](https://stackoverflow.com/questions/16774966/how-to-merge-datagridview-cell-in-winforms). Finally, if you intend to export to Excel cell merging is also available — Panagiotis Kanavos, May 21 '18 at 14:06

George Menoutis · Answer 1 · 2018-05-21T14:05:14.167

0

Try the following:

select date,
    'Completed: '+sum(case [Visit Type] when 'Completed' then [Count] else 0 end)
    +' '+char(10)+'Late Cancelled: '+sum(case [Visit Type] when 'Late Cancelled' then [Count] else 0 end)
    +' '+char(10)+'Bumped: '+sum(case [Visit Type] when 'Bumped' then [Count] else 0 end)
    +' '+char(10)+'No Show: '+sum(case [Visit Type] when 'No Show' then [Count] else 0 end)
    +' '+char(10)+'Cancelled: '+sum(case [Visit Type] when 'Cancelled' then [Count] else 0 end)
    as comment
from yourtable
group by date

edited May 21 '18 at 14:05

answered May 21 '18 at 14:02

George Menoutis

6,894
3
19
43

This won't work if the data needs to be displayed in an HTML page. It probably won't work in *any* OS either expect the old MacOS - `char(10)` is `\r `. *Cell merging* isn't something that should be done using SQL – Panagiotis Kanavos May 21 '18 at 14:05

score 0 · Answer 2 · answered May 21 '18 at 14:40

As already said, not really the way you should be approaching this, grouping of this sort would not be done in SQL.

You can however help things along a bit by grouping the data into XML or JSON if your front end can handle that. Have a look at the following:

DECLARE @myTable AS TABLE (Date DATE
                          ,VisitType NVARCHAR(20)
                          ,Count INT
                          )

INSERT  INTO @myTable
VALUES  ('2018-6-3', 'Completed', 9),
        ('2018-6-3', 'Late Cancelled', 1),
        ('2018-6-3', 'No Show', 2),
        ('2018-6-13', 'Completed', 5),
        ('2018-6-13', 'Bumped', 1),
        ('2018-6-13', 'No Show', 3)

SELECT  Date
,       (SELECT VisitType, [Count] FROM @myTable B WHERE B.Date = A.Date FOR XML AUTO ) Result
FROM    @myTable A
GROUP BY DATE

You can play with the xml parameters to get the exact format you want. Thats well documented online.

Merge multiple rows into one

2 Answers2