In this article I want to discuss a relatively simple problem which often comes up in different variations in MSDN Transact-SQL forum.

 

Introduction


There are not that many original problems in Transact-SQL. Most of the problems have well known solutions. The only tricky part in finding a solution to a common problem is in figuring out the type of the problem. In this article I want to show one such common problem - displaying grouped query results in one line.


Problem Definition


I want to discuss this problem based on a recent question in MSDN Transact-SQL forum How to put grouped results all on one line.
In this thread the topic starter was very kind to provide the definition of the table along with the sample data and his attempts at solving a problem.

CREATE TABLE #a
(
 PMT_ID INT IDENTITY(1,1),ACCT_NO INT, PMT_TYPE CHAR(1), PMT_AMT MONEY, PMT_DATE DATE)
 
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 450, '2012-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 100, '2012-12-01')
 
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 351, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 352, '2012-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 353, '2012-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 102, '2012-12-01')
 
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 850, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 200, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 880, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 201, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 855, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 203, '2013-12-01')
 
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'P', 451, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 1, 'I', 99, '2013-12-01')
 
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 352, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 101, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 353, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 103, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'P', 354, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 2, 'I', 103, '2013-12-01')
 
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 851, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 199, '2013-09-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 881, '2013-10-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 201, '2013-11-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'P', 854, '2013-12-01')
INSERT INTO #a ( ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE ) VALUES  ( 3, 'I', 205, '2013-12-01')
 
SELECT PMT_ID, ACCT_NO, PMT_TYPE, PMT_AMT, PMT_DATE
FROM #a

He wanted to create a new column for each payment type and also create columns for Year To Date payment types.

Solution


You can probably see that this problem is a variation of the PIVOT (cross-tab) problem. And for the PIVOT we have a well known CASE based solution:

SELECT ACCT_NO,
SUM(CASE WHEN PMT_TYPE = 'P' THEN PMT_AMT END) TOT_PRINCIPAL_PAID,
SUM(CASE WHEN PMT_TYPE = 'I' THEN PMT_AMT END) TOTAL_INTEREST_PAID,
SUM(CASE WHEN PMT_TYPE = 'P' 
AND PMT_DATE >=dateadd(YEAR, DATEDIFF(year, '19010101', CURRENT_TIMESTAMP),'19000101')
AND PMT_DATE < dateadd(YEAR, DATEDIFF(year, '19000101', CURRENT_TIMESTAMP),'19000101')
 
THEN PMT_AMT END) TOTAL_PRINCIPAL_PAID_YTD,
SUM(CASE WHEN PMT_TYPE = 'I' 
AND PMT_DATE >=dateadd(YEAR, DATEDIFF(year, '19010101', CURRENT_TIMESTAMP),'19000101')
AND PMT_DATE < dateadd(YEAR, DATEDIFF(year, '19000101', CURRENT_TIMESTAMP),'19000101')
 
THEN PMT_AMT END) TOTAL_INTEREST_PAID_YTD
 
FROM #a
 
GROUP BY ACCT_NO

I also changed the original date related code to use date ranges instead of checking the YEAR of the pmt_date as it was in the original code by the topic starter. Using date ranges allows us to utilize indexes on the date column. 

Conclusion


As you saw in this article, the solution to this common problem is very simple - it is a CASE based PIVOT. The only difficult part in the solution is to figure out which of the common problem types this problem is. Once you know many different types of Transact-SQL problems, you may be able to solve all of them easily.


See Also



This article participated in the TechNet Guru Contributions for January and won the Bronze Medal.