SQL - Group By Into Separate Columns

group-by, sql, sql-server, sql-server-2008

Solution

You can use an aggregate function with a `CASE` to covert the rows into columns:

SELECT CAST(TransDate AS DATE) AS [TransDate],
  ItemID,
  count(case when TransactionTypeID=20 then TransactionTypeID end) Amt_20,
  count(case when TransactionTypeID=21 then TransactionTypeID end) Amt_21
FROM Transactions
WHERE 
    TransDate BETWEEN '2013-01-01 10:00:00' AND '2013-02-01 10:00:00'
    AND TransactionTypeID IN (20,21)
GROUP BY CAST(TransDate AS DATE),ItemID;

Since you are using SQL Server this can also be written using the `PIVOT` function:

select TransDate,
  ItemId,
  [20] as Amt_20,
  [21] as Amt_21
FROM
(
  SELECT CAST(TransDate AS DATE) AS [TransDate],
    ItemID,
    TransactionTypeID
  FROM Transactions
  WHERE TransDate BETWEEN '2013-01-01 10:00:00' AND '2013-02-01 10:00:00'
    AND TransactionTypeID IN (20,21)
) d
pivot
(
  count(TransactionTypeID)
  for TransactionTypeID in ([20], [21])
) piv

Problem

Using SQL Server 2008 R2. I'm not sure if this is possible, but I would like to group by two columns and take their differences and place them into new columns. It's probably easier to explain with an example: ``` BEFORE:: [DATE] [ID] [AMT] [TYPE] 2013-01-11 36374DCD-47FE-48D8-8E70-8D3B37385311 2 20 2013-01-11 36374DCD-47FE-48D8-8E70-8D3B37385311 10 21 2013-01-11 4434E2D5-1D08-45FA-AADF-F653BF9A0D97 4 20 2013-01-11 4434E2D5-1D08-45FA-AADF-F653BF9A0D97 4 21 AFTER:: [DATE] [ID] [AMT 20] [AMT 21] 2013-01-11 36374DCD-47FE-48D8-8E70-8D3B37385311 2 10 2013-01-11 4434E2D5-1D08-45FA-AADF-F653BF9A0D97 4 4 ``` This is the SQL I have so far: ``` SELECT CAST(TransDate AS DATE) AS [TransDate],ItemID,COUNT(TransactionTypeID) AS [TransAmt], TransactionTypeID FROM Transactions WHERE TransDate BETWEEN '2013-01-01 10:00:00' AND '2013-02-01 10:00:00' AND TransactionTypeID IN (20,21) GROUP BY CAST(TransDate AS DATE),ItemID,TransactionTypeID ``` Any help is appreciated, thanks!

Original source