Solved

Merge lines of data based on value of a field

Posted on 2011-09-28
1
187 Views
Last Modified: 2012-05-12
I have SQL 2005 view that has lines of data that I need to merge into new columns.
Example: (see attached spreadsheet)
If AccountType is AA then place value in Period1
If AccountType is B1 then place value in Budget 1 - problem is that I want these in the same line for my reports.
On attached spreadsheet the top version is what I am trying to get. The bottom is what I currently have. Basically I am trying to add a new column called Budget1 thru 12 basically that places the value if AccountType is B1.

I currenlty have the following:
CASE WHEN a.ActivityType='B1' THEN Period1 Else 0 END as Budget1
but this gives me seperate line of data for Budget and Period
sample-data.xls
0
Comment
Question by:allenkent
1 Comment
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 36814308
Hi,

you mean to say this.....

- Bhavesh
SELECT AccountType, SUM(Budget)Budget, SUM(Period)Period
FROM
(
SELECT AccountType, 
	CASE WHEN AccountType = 'AA' THEN Value ELSE 0 END AS Budget,
	CASE WHEN AccountType = 'A1' THEN Value ELSE 0 END AS Period
FROM Table1
)AS A
Group By AccountType

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 37
Insert statement is inserting duplicate records 15 61
MS SQL 2005 Srink database in chunks 4 57
Not selecting duplicate data 6 52
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now