CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
RobinВricks
RobinВricks

Reputation: 93

How to create a summary table?

I have rows:

PLACE   DATE_OPEN   SUM
-----------------------
PLACE1  03.09.2018  2
PLACE2  03.09.2018  2
PLACE1  04.09.2018  8
PLACE1  05.09.2018  9
PLACE2  05.09.2018  1

I get the rows using this SQL query:

SELECT
    SB AS [PLACE], 
    CAST(OPEN_TIME AS DATE) AS [DATE_OPEN], 
    COUNT(ID) AS [SUM]
FROM
    [Table]
WHERE
    SB IN ('PLACE1', 'PLACE2')
GROUP BY 
    SB, CAST(OPEN_TIME AS DATE)

I want to get a table in the form of a summary table, is it possible to do this using SQL?

Example of the desired output:

PLACE   DATE_OPEN   
        03.09.2018  04.09.2018  05.09.2018
PLACE1  2           8           9
PLACE2  2                       1

Upvotes: 0

Views: 960

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

The closest is PIVOT:

WITH Demo AS
(
    SELECT * FROM (VALUES
    ('PLACE1', '03.09.2018', 2),
    ('PLACE2', '03.09.2018', 2),
    ('PLACE1', '04.09.2018', 8),
    ('PLACE1', '05.09.2018', 9),
    ('PLACE2', '05.09.2018', 1)) T(PLACE, DATE_OPEN, [SUM])
)
SELECT * FROM Demo
PIVOT (SUM([Sum]) FOR Date_Open IN ([03.09.2018],[04.09.2018],[05.09.2018])) P

Result

PLACE  03.09.2018  04.09.2018  05.09.2018
------ ----------- ----------- -----------
PLACE1 2           8           9
PLACE2 2           NULL        1

Upvotes: 1

Related Questions