Solved

Creating Rows in a SQL View from existing columns

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select * from 6 41
Why do I get extra rows when I do inner join? 12 39
CONVERT date time to a different time zone. 2 55
Problem with SqlConnection 4 171
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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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