Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merge lines of data based on value of a field

Posted on 2011-09-28
1
Medium Priority
?
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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