How to group subtotals on the same row by date, by code

Question

I couldn't find an equivalent question on here for this question. Apologies if this is a repeat Basically I have a table with transactions. Each transaction has a code and a datetime stamp. I want to be able to create a SQL query so that the results look something like this

+------------+--------+--------+-------+--------+-------+--------+
|    DATE    | CODE1  | COUNT1 | CODE2 | COUNT2 | CODE3 | COUNT3 |
+------------+--------+--------+-------+--------+-------+--------+
| 2017-01-01 | George |     12 | John  |     10 | Ringo |    114 |
+------------+--------+--------+-------+--------+-------+--------+

I currently have a query that I can pull the subtotals on individual lines, i.e:

SELECT CONVERT(mytime AS DATE), code, COUNT(*) FROM transactiontable
GROUP BY CONVERT(mytime AS DATE), code
ORDER BY CONVERT(mytime AS DATE), code

Would give me

DATE             CODE       COUNT
-----------------------------------
2017-01-01       George      12
2017-01-01        John       10

etc ...

I don't currently have a separate table for the codes, but I am considering it.

Thanks !

Answer 1

You also can use PIVOT for making this.

DECLARE @Table TABLE (DATE DATETIME,  CODE  VARCHAR(10), [COUNT] INT)
INSERT INTO @Table

VALUES
('2017-01-01','George',12),
('2017-01-01','John',10)

;WITH CTE AS 
(
    SELECT RN = ROW_NUMBER() OVER (ORDER BY DATE), * FROM @Table
)
SELECT * FROM 
        (SELECT DATE, CONCAT('CODE',RN) RN, CODE Value FROM CTE
        UNION ALL 
        SELECT DATE, CONCAT('COUNT',RN) RN,  CONVERT(VARCHAR,[COUNT]) Value FROM CTE
        ) SRC
PIVOT (MAX(Value) FOR RN IN ([CODE1],[COUNT1],[CODE2],[COUNT2])) PVT

Result:

DATE        CODE1       COUNT1      CODE2    COUNT2 
----------- ----------- ----------- -------- -------
2017-01-01  George      12          John     10