November 30, 2011 at 5:38 am
Hi,
We are struggling with the development of a data view which allows us to group data into batches of 5 years
The raw data is available in the following format - where commas are new columns.
Id 1, Id 2, Row Description, Year Num, Amount
However, we want to output it to Word merge table using the following view format
Id 1, Id 2, Row Description 1, Year 1 amount, Year 2 amount, Year 3 amount, Year 4 amount, Year 5 amount
Id 1, Id 2, Row Description 2, Year 1 amount, Year 2 amount, Year 3 amount, Year 4 amount, Year 5 amount
Id 1, Id 2, Row Description 3, Year 1 amount, Year 2 amount, Year 3 amount, Year 4 amount, Year 5 amount
Id 1, Id 2, Row Description 1, Year 6 amount, Year 7 amount, Year 8 amount, Year 9 amount, Year 10 amount
Id 1, Id 2, Row Description 2, Year 6 amount, Year 7 amount, Year 8 amount, Year 9 amount, Year 10 amount
Id 1, Id 2, Row Description 3, Year 6 amount, Year 7 amount, Year 8 amount, Year 9 amount, Year 10 amount
so forth for as many records as exists in groups of 5 years (upto a max of 25 years).
Does anyone know if this is possible using a view or set of of views / stored procs?
Thanks,
Jon
November 30, 2011 at 6:57 am
Hi Jon, welcome to the forum.
Can you post up some sample data? There's a link in my sig which shows how to do this - well worth a read for hints and tips on how best to use the forum.
At first glance, possible solutions would use either a pivot or a cross-tab. Since your data is structured row-wise as well as column-wise and requires quite intensive processing, it might be better encapsulated in a stored procedure rather than a view - would this be a problem?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 30, 2011 at 7:24 am
Thanks for the advice....
--===== Create the test table with
CREATE TABLE #projectFinancials (
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ProjectID INT,
rowDescription CHAR(100),
Year INT,
Amount MONEY )
INSERT INTO projectFinancials (1, 1, 'Cost Item 1', 1, 40000)
INSERT INTO projectFinancials (2, 1, 'Cost Item 1', 2, 42000)
INSERT INTO projectFinancials (3, 1, 'Cost Item 1', 3, 43000)
INSERT INTO projectFinancials (4, 1, 'Cost Item 1', 4, 44000)
INSERT INTO projectFinancials (5, 1, 'Cost Item 1', 5, 45000)
INSERT INTO projectFinancials (6, 1, 'Cost Item 1', 6, 46000)
INSERT INTO projectFinancials (7, 1, 'Cost Item 1', 7, 47000)
INSERT INTO projectFinancials (8, 1, 'Cost Item 1', 8, 48000)
INSERT INTO projectFinancials (9, 1, 'Cost Item 2', 1, 40000)
INSERT INTO projectFinancials (10, 1, 'Cost Item 2', 2, 42000)
INSERT INTO projectFinancials (11, 1, 'Cost Item 2', 3, 43000)
INSERT INTO projectFinancials (12, 1, 'Cost Item 2', 4, 44000)
INSERT INTO projectFinancials (13, 1, 'Cost Item 2', 5, 45000)
INSERT INTO projectFinancials (14, 1, 'Cost Item 2', 6, 46000)
INSERT INTO projectFinancials (15, 1, 'Cost Item 2', 7, 47000)
INSERT INTO projectFinancials (16, 1, 'Cost Item 2', 8, 48000)
With the above data I would like an output in the following format
the output would have would be have 4 rows:
Cost item 1 yrs1-5
Cost item 2 yrs1-5
Cost item 1 yrs6-8
Cost item 2 yrs6-8
(
ProjectID INT,
rowDescription CHAR(100),
YearColumn1 MONEY,
YearColumn2 MONEY,
YearColumn3 MONEY,
YearColumn4 MONEY,
YearColumn5 MONEY,
)
Delivering it through a stored procedure would be fine.
November 30, 2011 at 7:54 am
Hey Jon, thanks loads for posting up sample data - it took a moment to tweak the script but no worries. Try this:
;WITH CTE AS (
SELECT
ID,
ProjectID,
rowDescription,
[Year],
Amount,
RowGroup = DENSE_RANK() OVER(ORDER BY rowDescription, CASE WHEN [Year] < 6 THEN 1 ELSE 2 END)
FROM #projectFinancials
)
SELECT rowDescription,
YearColumn1 = MAX(CASE WHEN [Year] IN (1,6) THEN Amount END),
YearColumn2 = MAX(CASE WHEN [Year] IN (2,7) THEN Amount END),
YearColumn3 = MAX(CASE WHEN [Year] IN (3,8) THEN Amount END),
YearColumn4 = MAX(CASE WHEN [Year] IN (4,9) THEN Amount END),
YearColumn5 = MAX(CASE WHEN [Year] IN (5,10) THEN Amount END)
FROM CTE
GROUP BY rowDescription, RowGroup
ORDER BY rowDescription, RowGroup
Edit: added ORDER BY
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply