Solved

Creating Rows in a SQL View from existing columns

Posted on 2011-09-29
4
155 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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