Formatting SQL query to display differently

Question

I am a beginner to writing queries and have been racking my brain trying to figure out what the best approach to this is. I have created a temp table and wrote a query to give me the following data this result from the following data.

SELECT Temp.OrderType,Temp.OrderDate,COUNT(*) as prodCount
FROM Temp
GROUP BY Temp.OrderType, Temp.OrderDate
ORDER BY Temp.OrderType;

RESULTS

OrderType    OrderDate                  prodCount
1            2012-06-04 00:00:00.000    1
1            2012-06-06 00:00:00.000    1
2            2012-06-07 00:00:00.000    2
3            2012-06-05 00:00:00.000    1
3            2012-06-06 00:00:00.000    2
3            2012-06-07 00:00:00.000    1
7            2012-06-05 00:00:00.000    1
11           2012-06-07 00:00:00.000    1

How can I go about to get the data to display in this format instead with the count totals going under the date ? Date1 Date2 etc, are 6/04, 6/05, 6/06, 6/07. Any help or guidance is appreciated. Thanks you!

OrderType Date1 Date2 Date3 Date4
1            1                1
2                             2
3                1      2     1
4
7                1
11                      1

SAMPLE DATA

OrderDate                  OrderType
2012-06-06 00:00:00.000    1
2012-06-04 00:00:00.000    1
2012-06-05 00:00:00.000    7
2012-06-05 00:00:00.000    3
2012-06-06 00:00:00.000    3
2012-06-06 00:00:00.000    3
2012-06-07 00:00:00.000    3
2012-06-07 00:00:00.000    2
2012-06-07 00:00:00.000    2
2012-06-07 00:00:00.000    3
2012-06-07 00:00:00.000    11

What you're looking for is typically called a "pivot" or "crosstab" query. How to write that depends on which database you're using. — Cheran Shunmugavel
– Cheran Shunmugavel, Commented Jun 10, 2012 at 23:21
Also, because your table will keep acquiring dates (columns, if you writer the query as you intend) I would recommend you write your query with the Order Types as columns, and the dates as rows. Your query will be a lot simpler and will scale with your data. — dbrosier
– dbrosier, Commented Jun 11, 2012 at 0:21
Thanks all for the replies! This is a MSSQL DB. I will try the suggestion by dbrosier and check back in. — user1447909
– user1447909, Commented Jun 11, 2012 at 3:53
In your particular case the version of the DBMS is also important. SQL Server 2005 and later versions have a feature called PIVOT that greatly simplifies this kind of queries. — Andriy M
– Andriy M, Commented Jun 11, 2012 at 5:02

dbrosier · Accepted Answer · 2012-06-11 04:48:26Z

1

If your DB engine is MSSQL, try this:

SELECT OrderDate, 
OrderType_1 = SUM(CASE OrderType WHEN 1 THEN 1 ELSE 0 END),
OrderType_2 = SUM(CASE OrderType WHEN 2 THEN 1 ELSE 0 END ),
OrderType_3 = SUM(CASE OrderType WHEN 3 THEN 1 ELSE 0 END),
OrderType_4 = SUM(CASE OrderType WHEN 4 THEN 1 ELSE 0 END),
OrderType_7 = SUM(CASE OrderType WHEN 7 THEN 1 ELSE 0 END),
OrderType_11= SUM(CASE OrderType WHEN 11 THEN 1 ELSE 0 END)
FROM Temp
GROUP BY OrderDate
ORDER BY OrderDate

edited Jun 11, 2012 at 4:48

answered Jun 11, 2012 at 0:48

dbrosier

3304 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dbrosier Over a year ago

Forgot to"end" the case statements. Oooops.

Andriy M Over a year ago

Um, yes, and your query should work in any SQL product too (or in most of them, at least). Only the output should be the other way round: dates should go as columns, types as rows. Also, it seems like the OP wants the column names (the dates) to be dynamic (well, that is my reading of the question, anyway).

EricZ · Accepted Answer · 2012-06-11 16:17:25Z

1

Please try this

   DECLARE @sql varchar(4000)

    SET @sql =
        '
        SELECT  * 
        FROM    
        (
            SELECT OrderType,CONVERT(VARCHAR(10),OrderDate,112) AS OrderDate,prodCount 
            FROm t 
        )  st
        PIVOT 
        (
        SUM(prodCount)
        FOR OrderDate IN ( '
        +  
        STUFF(
            (
             SELECT ',['  + OrderDate + ']'
             FROM 
                (SELECT distinct CONVERT(VARCHAR(10),OrderDate,112) AS OrderDate  FROM t)  a
             FOR XML PATH('')
             ),1,1,'')
        + ')
        ) as pvt
        ORDER BY pvt.OrderType
        '

    PRINT @sql
    EXEC(@sql)

SQLFiddle link

answered Jun 11, 2012 at 16:17

EricZ

6,2151 gold badge33 silver badges30 bronze badges

Collectives™ on Stack Overflow

Formatting SQL query to display differently

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related