allenkent
asked on
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
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
Existing-SQL-View-Results.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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","
Incorrect Syntac near","
Open in new window