• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Creating Rows in a SQL View from existing columns

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
allenkent
Asked:
allenkent
  • 2
1 Solution
 
appariCommented:
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
 
appariCommented:
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
 
akku101Commented:
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
 
allenkentAuthor Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now