SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouping results by matrix dataset


Grouping results by matrix dataset

Author
Message
stuart.cox
stuart.cox
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 96
Hi,

I am struggling on a requirement that involves grouping matrix rows with the same resultant dataset.

For example:

Accnt MatrixColA MatrixColB MatrixColC
1 0 1 0
2 0 1 0
3 2 1 0
4 0 0 1

Is there a way to group accnt 1 and 2 as they share the same values for matrixColA, matrixColB, and matrixColC? This would mean that for those values {0,1,0}, there is a count of two. For the other values {2,1,0} and {0,0,1}, there is a count of one respectively.

As it is a matrix there could be any number of matrix columns and I'd need the solution to work.

The underlying dataset for the above would look like:

Accnt MatrixCol
1 B
2 B
3 A
3 A
3 B
4 C

Can anybody see a solution to this? I have tried in the SQL to group the records by making use of "STUFF" to create a new column such as A,A,B (for accnt 3) but this takes too long when I have a large dataset.

Thanks in anticipation,

Stu
stuart.cox
stuart.cox
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 96
SOLVED BigGrin

I solved this by adding an additional column to my report called, for example, "relationship set".

The value of relationship set is determined by an expression:

=JOIN(LOOKUPSET(Fields!Accnt.Value,Fields!Accnt.Value,Fields!matrixCol.Value,"Main"),", ")

Where "Main" is the dataset that you are using (I am only using one dataset so looking up itself).

The performance seems to be great too even with large datasets.

The results for this example would be:

Accnt Relationship Set
1 B
2 B
3 A, A, B
4 C

The relationship set can then be used to group common values within the matrix.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search