Can I format results from SQL query like a Excel sheet

Question

I am running a query for every truck in our fleet to see the mileage and amount of fuel used over a month, quarter or year time frame. So my columns are Date, Truck, Miles. Formatting the results is time consuming because all the trucks' data are listed together like below.

   Date   Truck   Miles
   1/1/15   101     542
   1/2/15   101     342

Is there a way to format the query results when coping to Excel or running the query as a macro in Excel so the results are displayed like a 3d table like below?

   Truck  1/1/15  1/2/15  1/3/15
    101      542     342     741
    102      121     214     141
    103      541     741     874

You might want to check this http://stackoverflow.com/questions/12074939/get-rows-as-columns-sql-server-dynamic-pivot-query or this http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query — Saagar Elias Jacky, Apr 08 '15 at 20:54
possible duplicate of [SQL Rows to Columns](http://stackoverflow.com/questions/1187460/sql-rows-to-columns) — Ken White, Apr 08 '15 at 20:54
http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query is probably what you want — Dan Field, Apr 08 '15 at 21:19

score -2 · Answer 1 · edited Aug 19 '16 at 21:19

You have to create a stored procedure and cursor like this example below.

SET NOCOUNT ON
DECLARE @au_id varchar(11),
 @au_fname varchar(20),
 @au_lname varchar(40),
 @message varchar(80),
 @title varchar(80)
PRINT('-------- Utah Authors report --------')
DECLARE authors_cursor CURSOR FOR
(SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = 'UT'
ORDER BY au_id)
OPEN authors_cursor
FETCH FIRST FROM authors_cursor INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT(' ')
   SET @message = '----- Books by Author: ' + @au_fname + ' ' + @au_lname
   PRINT(@message)
   -- Declare an inner cursor based
   -- on au_id from the outer cursor.
   DECLARE titles_cursor CURSOR FOR
   (SELECT t.title
   FROM titleauthor ta, titles t
   WHERE ta.title_id = t.title_id AND
   ta.au_id = @au_id)   -- Variable value from the outer cursor
   OPEN titles_cursor
   FETCH FIRST FROM titles_cursor INTO @title
   IF @@FETCH_STATUS <> 0
      PRINT('         <<No Books>>')
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @message = '         ' + @title
      PRINT @message
      FETCH NEXT FROM titles_cursor INTO @title
   END
   CLOSE titles_cursor
   DEALLOCATE titles_cursor
   -- Get the next author.
   FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameEND
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

No no no, the answer would be to use PIVOT, either static or dynamic if the list of items can change. This example code that you copied from the [SQL Server 2000 Book Online](https://technet.microsoft.com/en-us/library/aa258831%28v=sql.80%29.aspx) really has nothing to do with this at all. — jpw, Apr 08 '15 at 21:48

Can I format results from SQL query like a Excel sheet

1 Answers1