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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.