1

i got a question here.

I'm trying to select all the date inside my database (1-31), instead of using 1,[2],[3]... manually way is there any easier way to select them all?

I tried using some dumb way like:

SUM(TOTAL) FOR DATE IN (*)

SUM(TOTAL) FOR DATE IN ([*])

Here is my query:

SELECT * FROM 
(
    SELECT  BRANCH.NAME,SALES.TOTAL AS TOTAL,TIME.DATE 
    FROM SALES
    INNER JOIN BRANCH 
    ON SALES.BRANCH_ID=BRANCH.BRANCH_ID
    INNER JOIN TIME 
    ON SALES.TIME_ID=TIME.TIME_ID
    WHERE TIME.MONTH='APR'
)AS TABLE1
PIVOT (
    SUM(TOTAL) FOR DATE IN ([1],[2],[3],[5],[6],[7],[8],[9],[10])
) PIVOTTABLE

Also is there possible to create an extra grand total column by SQL like excel do ? SAMPLE: enter image description here

Jake Cube
  • 143
  • 1
  • 2
  • 17
  • 1
    No, you need to list out the columns. You can use dynamic SQL to generate the list for you, but if the size of the list isn't going to change then you're much better off just listing them out. – Tom H Apr 20 '16 at 20:07
  • you might be able to use ROLLUP to get your grand Total Row and use SUM() OVER (Partition By) in your TABLE1 query for the grand total column – JamieD77 Apr 20 '16 at 20:19
  • The first part of your question has been answered many times on this site alone. Search for "Dynamic Pivot SQL Server" or Google it. Here's something: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query The second part of your question is not something you normally do in SQL. It's possible (you can use a UNION query and sum on those columns to get the total), but normally most people rely on a program to finish that part. – Boyd P Apr 20 '16 at 20:34

0 Answers0