Solved

Creating Rows in a SQL View from existing columns

Posted on 2011-09-29
4
154 Views
Last Modified: 2012-06-25
I have a SQL-View that has columns that represent months (periods). These are budget columns for the each month for the year. What I need is the data in each of these columns placed in a new column but the heading of this column is now a single column named Period and they have value  0 thru 12 (see attached samples and attached code).

Note code below:
Notice how I have all the columns named a.PeriodXActivityAmount -  these 13 columns I want to be a single column named "Period" and they would basically have a value of 0 thru 12. Inside would have the value (my data rows would basically increase by 13 times the numbers of rows)

 format-I-need.xls

SELECT     a.FiscalYear, a.ActivityType, m.MasterAccount, a.Period0ActivityAmount, a.Period1ActivityAmount, a.Period2ActivityAmount, a.Period3ActivityAmount, 
                      a.Period4ActivityAmount, a.Period5ActivityAmount, a.Period6ActivityAmount, a.Period7ActivityAmount, a.Period8ActivityAmount, 
                      a.Period9ActivityAmount, a.Period10ActivityAmount, a.Period11ActivityAmount, a.Period12ActivityAmount, 
                      m.MasterAccountCurrentBalance
FROM         FS_GLActualBudgetActivity AS a WITH (nolock) INNER JOIN
                      FS_GLMasterAccount AS m WITH (nolock) ON m.GLMasterAccountKey = a.GLMasterAccountKey
WHERE     (a.ActivityType = 'B1') 
ORDER BY a.FiscalYear, a.ActivityType, m.MasterAccount

Open in new window

Existing-SQL-View-Results.jpg
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
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 36818979
try this

Select 
FiscalYear, ActivityType, MasterAccount, MasterAccountCurrentBalance,
periodNo, period 
From (
SELECT     a.FiscalYear, a.ActivityType, m.MasterAccount, 
	a.Period0ActivityAmount Period0, a.Period1ActivityAmount Period1, 
	a.Period2ActivityAmount Period2, a.Period3ActivityAmount Period3, 
	a.Period4ActivityAmount Period4, a.Period5ActivityAmount Period5, 
	a.Period6ActivityAmount Period6, a.Period7ActivityAmount Period7, 
	a.Period8ActivityAmount Period8, a.Period9ActivityAmount Period9, 
	a.Period10ActivityAmount Period10, a.Period11ActivityAmount Period11, 
	a.Period12ActivityAmount Period12, 
                      m.MasterAccountCurrentBalance
FROM         FS_GLActualBudgetActivity AS a WITH (nolock) INNER JOIN
                      FS_GLMasterAccount AS m WITH (nolock) ON m.GLMasterAccountKey = a.GLMasterAccountKey
WHERE     (a.ActivityType = 'B1') ) p
unpivot (period for periodNo in
( Period0, Period1, Period2, Period3, Period4, Period5, Period6,
	 Period7, Period8, Period9, Period10, Period11, Period12)
) as unpvt
ORDER BY FiscalYear, ActivityType, MasterAccount

Open in new window

0
 
LVL 39

Expert Comment

by:appari
ID: 36818987
I just checked the format you want file, to get the period numbers try this

Select 
FiscalYear, ActivityType, MasterAccount, MasterAccountCurrentBalance,
periodNo,  Period  
From (
SELECT     a.FiscalYear, a.ActivityType, m.MasterAccount, 
	a.Period0ActivityAmount [0], a.Period1ActivityAmount [1], 
	a.Period2ActivityAmount [2], a.Period3ActivityAmount [3], 
	a.Period4ActivityAmount [4], a.Period5ActivityAmount [5], 
	a.Period6ActivityAmount [6], a.Period7ActivityAmount [7], 
	a.Period8ActivityAmount [8], a.Period9ActivityAmount [9], 
	a.Period10ActivityAmount [10], a.Period11ActivityAmount [11], 
	a.Period12ActivityAmount [12], 
                      m.MasterAccountCurrentBalance
FROM         FS_GLActualBudgetActivity AS a WITH (nolock) INNER JOIN
                      FS_GLMasterAccount AS m WITH (nolock) ON m.GLMasterAccountKey = a.GLMasterAccountKey
WHERE     (a.ActivityType = 'B1') ) p
unpivot (period for [No in
( [0], [1], [2], [3], [4], [5], [6],
	 [7], [8], [9], [10], [11], [12])
) as unpvt
ORDER BY FiscalYear, ActivityType, MasterAccount

Open in new window

0
 
LVL 2

Expert Comment

by:akku101
ID: 36890264
Unpivot will be best solution, check this query
Select
		UnPivt.FiscalYear, 
		UnPivt.ActivityType, 
		UnPivt.MasterAccount, 
		UnPivt.MasterAccountCurrentBalance,
		tblPivot.Property as 'Date_Name'
		tblPivot.Value  as 'Valuee'
(
	SELECT    a.FiscalYear, 
			  a.ActivityType, 
			  m.MasterAccount, 
			  m.MasterAccountCurrentBalance
			  CONVERT(sql_variant,a.Period0ActivityAmount) as Period0ActivityAmount, 
			  CONVERT(sql_variant,a.Period1ActivityAmount) as Period1ActivityAmount, 
			  CONVERT(sql_variant,a.Period2ActivityAmount) as Period2ActivityAmount, 
			  CONVERT(sql_variant,a.Period3ActivityAmount) as Period3ActivityAmount, 
			  CONVERT(sql_variant,a.Period4ActivityAmount) as Period4ActivityAmount, 
			  CONVERT(sql_variant,a.Period5ActivityAmount) as Period5ActivityAmount, 
			  CONVERT(sql_variant,a.Period6ActivityAmount) as Period6ActivityAmount, 
			  CONVERT(sql_variant,a.Period7ActivityAmount) as Period7ActivityAmount, 
			  CONVERT(sql_variant,a.Period8ActivityAmount) as Period8ActivityAmount, 
			  CONVERT(sql_variant,a.Period9ActivityAmount) as Period9ActivityAmount, 
			  CONVERT(sql_variant,a.Period10ActivityAmount) as Period10ActivityAmount, 
			  CONVERT(sql_variant,a.Period11ActivityAmount) as Period11ActivityAmount, 
			  CONVERT(sql_variant,a.Period12ActivityAmount) as  Period12ActivityAmount
			  
	FROM         
		FS_GLActualBudgetActivity AS a WITH (nolock) 
		INNER JOIN FS_GLMasterAccount AS m WITH (nolock) ON m.GLMasterAccountKey = a.GLMasterAccountKey
	WHERE     
		(a.ActivityType = 'B1') 
	ORDER BY 
		a.FiscalYear,	
		a.ActivityType, 
		m.MasterAccount
) UnPivt  UNPIVOT (Value For Property In 
  (
		Period0ActivityAmount, 
		Period1ActivityAmount, 
		Period2ActivityAmount, 
		Period3ActivityAmount, 
		Period4ActivityAmount, 
		Period5ActivityAmount, 
		Period6ActivityAmount, 
		Period7ActivityAmount, 
		Period8ActivityAmount, 
		Period9ActivityAmount, 
		Period10ActivityAmount,
		Period11ActivityAmount,
		Period12ActivityAmount
)) as tblPivot

Open in new window

0
 

Author Comment

by:allenkent
ID: 36904581
Appari - your first view works but it gives the values as Period1, Period2, and so on. The second one keeps giving me an error. I tried to solve the error but it keeps happening.
Incorrect Syntac near","
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

733 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