SQL count and group then pivot

Question

So, I have been having this problem and I guess I am just too overloaded to figure it out. I have a database that I need to count from. That's all good. But where I run into a problem is i need to store it as only 2 rows, one for all the dates and one for the count. Here is an example:

obj_name | date_made
--------------------
   1     | 2016-3-04
   2     | 2016-5-23
   3     | 2016-5-23
   4     | 2016-5-23
   5     | 2016-6-07
   6     | 2016-6-07
   7     | 2016-6-07
   8     | 2016-6-07
   9     | 2016-9-12
   10    | 2016-9-12

What I want is to count how many objects are created on a certain date, then return it as 2 rows - one with all the dates then one with all the counts

Row1 | 2016-3-04 |  2016-5-23 |  2016-6-07  |  2016-9-12
Row2 |     1     |      3     |      4      |      2

If anyone can help that would be much appreciated.

here is what I have so far, I can get all the info I need but as 2 columns and I need it as 2 rows

SELECT datem,
     SUM(num) AS total_num
FROM (
     SELECT date_made AS datem,
            obj_name,
            COUNT(1) AS num
     FROM db.tn
     GROUP BY 1,2
) sub
GROUP BY 1
ORDER BY 1 DESC

What is your RDBMS? What have you tried so far (include your query up until this point). — Igor, Oct 03 '17 at 19:34
so far this is what I have, I have it displayed as columns and just need to turn it — Ender_wigs, Oct 03 '17 at 19:42

score 0 · Answer 1 · answered Oct 03 '17 at 19:39

You can try a dynamic pivot query like below

   DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
    SELECT @cols = STUFF((SELECT  ',' + QUOTENAME(date_made) 
                        FROM tbl
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    SELECT @query = 
    'SELECT * FROM '+
    '(SELECT COUNT(1) count, date_made FROM tbl ) src '+
    ' pivot '+
    '( max(count) for date_made in ('+@cols+'))p'

    EXEC(@query)

score 0 · Answer 2 · answered Oct 03 '17 at 19:48

If I read this correctly, you are going to end up with an aggregate table that looks like this:

date_made | count
----------|------
2016-3-04 |  1
2016-5-23 |  3
2016-6-07 |  4
2016-9-12 |  2

And then you want to pivot the table on its side to look like the output in your initial question. Therefore, I think this is a repeat of this question:

Simple way to transpose columns and rows in Sql?

SQL count and group then pivot

2 Answers2