Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Creating Rows in a SQL View from existing columns

Posted on 2011-09-29
4
Medium Priority
?
158 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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