Sql Server 2008 - Transpose Data

I have Query Output:

NO              NAME                      BALANCE      AGE

1024      CUSTOMER 1               68938.51      359
1316      CUSTOMER 2                            0      1960
2949      CUSTOMER 3                      3150      212
4263      CUSTOMER 4                     80500      240
5344      CUSTOMER 5                      -5000      102
1343      CUSTOMER 6                     94050      958
1343      CUSTOMER 6                     94050      1197
1435      CUSTOMER 7                67053.35      193
1435      CUSTOMER 7                67053.35      112
1436      CUSTOMER 8                     11405      47

How can I Transpose this data to following format :

Account No   Account Name        Balance Total        0-30       31-60     61-90      91-120            > 120

1024              CUSTOMER 1                  68938.51                                                                   68938.51

Amounts show above is summarized data for that age and balance toal is sum of balance for that customer

Harsh08Asked:
Who is Participating?
 
Patrick MatthewsCommented:
Assuming the data is starting in a table or view...


SELECT No, Name, SUM(Balance) AS BalanceTotal,
    SUM(CASE WHEN Age < 31 THEN Balance ELSE 0 END) AS [0-30],
    SUM(CASE WHEN Age > 30 AND Age < 61 THEN Balance ELSE 0 END) AS [31-60],
    SUM(CASE WHEN Age > 60 AND Age < 91 THEN Balance ELSE 0 END) AS [61-90],
    SUM(CASE WHEN Age > 90 AND Age < 121 THEN Balance ELSE 0 END) AS [91-120],
    SUM(CASE WHEN Age > 120 THEN Balance ELSE 0 END) AS [>120]
FROM SomeTableOrView
GROUP BY No, Name
ORDER BY No
0
 
Adam MenkesC# ASP.NET DeveloperCommented:
Have you looked at PIVOT TABLE?

Below is a partial (non-working) example, but is getting close to what you want:

SELECT [No] AS "Account No.",
[0] AS "0-30", [1] AS "31-60", [2] AS "61-90", [3] AS "91-120", [4] AS ">120"
FROM
(SELECT [No], Age, Balance
    FROM PivotTest) AS SourceTable
PIVOT
(
  SUM(Balance)
  FOR Age IN ([0], [1], [2], [3], [4])
) AS PivotTable;
0
 
Adam MenkesC# ASP.NET DeveloperCommented:
SELECT [No] AS "Account No.",
[0] AS "0-30", [1] AS "31-60", [2] AS "61-90", [3] AS "91-120", [4] AS ">120"
FROM
(SELECT [No], Balance, AgeBracket = CASE
                                                                              WHEN Age <= 30 THEN 0
                                                                              WHEN Age BETWEEN 31 AND 60 THEN 1
                                                                              WHEN Age BETWEEN 61 AND 90 THEN 2
                                                                              WHEN Age BETWEEN 91 AND 120 THEN 3
                                                                              WHEN Age > 120 THEN 4
                                                                        END            
    FROM PivotTest) AS SourceTable
PIVOT
(
  SUM(Balance)
  FOR AgeBracket IN ([0], [1], [2], [3], [4])
) AS PivotTable
ORDER BY 1

PivotExample.PNG
0
 
Adam MenkesC# ASP.NET DeveloperCommented:
Sorry, forgot to include the Balance Totals column:


SELECT 
	PivotTable.[No] AS "Account No.", 
	x.TotalBalance AS "Balance Total",
	[0] AS "0-30", 
	[1] AS "31-60", 
	[2] AS "61-90", 
	[3] AS "91-120", 
	[4] AS ">120"
FROM
	(SELECT 
		[No], 
		Balance, 
		AgeBracket = CASE 
							WHEN Age <= 30 THEN 0
							WHEN Age BETWEEN 31 AND 60 THEN 1
							WHEN Age BETWEEN 61 AND 90 THEN 2
							WHEN Age BETWEEN 91 AND 120 THEN 3
							WHEN Age > 120 THEN 4
						 END
    FROM PivotTest) AS SourceTable
PIVOT
(
  SUM(Balance)
  FOR AgeBracket IN ([0], [1], [2], [3], [4])
) AS PivotTable

JOIN (
	SELECT [No], SUM(Balance) as TotalBalance
	FROM PivotTest
	GROUP BY [No]
) x
ON x.[No] = PivotTable.[No]
ORDER BY  PivotTable.[No]

Open in new window

PivotExampleWithTotal.PNG
0
 
Harsh08Author Commented:
Thaks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.