December 19, 2012 at 5:12 am
Hello,
Firstly apologies if this turns out to be a t-sql issue but as it is a report I am trying to produce I have put it in this forum.
I have been asked to provide a report. Currently the data returns as 1 row per dependant.
MemberNumber, dependantName
1111, Bob
1111, Charlie
1212, Lucy
1222, Laura
1222, Bill
1222, Gill
I need to be able to present the data in the following way (a column for each dependant against a membership number):
MemberNumber, Dependant1, Dependant2, Dependant3
1111, Bob, Charlie
1212, Lucy
1222, Laura, Bill, Gill
The problem I am facing is the number of dependants is an unknown figure so the report will need to create the columns dynamically, I initially thought a matrix would solve this but either the grouping won’t work in this scenario or I am going about it in the wrong way
Is it possible to do this using pivot query/matrix table or does the data need to be built up so that the report becomes a simple select from a temporary table?
Thanks
December 19, 2012 at 6:32 am
You can use the matrix, but you need to have the dataset modified.
Add a depenantNum colum and using the rank, or rownumber function number the dependants from 1 to n
and use the dependantnum filed as your column group on your matrix
MemberNumber, dependantNum, dependantName
1111, 1, Bob
1111, 2, Charlie
1212, 1, Lucy
1222, 1, Laura
1222, 2, Bill
1222, 3, Gill
December 19, 2012 at 6:43 am
Ray M (12/19/2012)
You can use the matrix, but you need to have the dataset modified.Add a depenantNum colum and using the rank, or rownumber function number the dependants from 1 to n
and use the dependantnum filed as your column group on your matrix
MemberNumber, dependantNum, dependantName
1111, 1, Bob
1111, 2, Charlie
1212, 1, Lucy
1222, 1, Laura
1222, 2, Bill
1222, 3, Gill
Thank you very much, this is working great!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply