Home > Paul Busby, Work > Generate unique X and Y coordinates or grid references from a row number

Generate unique X and Y coordinates or grid references from a row number

I was creating an SSRS report and using a matrix table. I needed to be able to position data within each group by. To do this i needed create a X and Y number for each item where the 2 together would be unique.

 

This is an example of what i wanted

Row number

x

y

1

0

1

2

0

2

3

0

3

4

1

1

5

1

2

6

1

3

7

2

1

8

2

2

9

2

3

 

And here is the SQL that creates it

 

SELECT  ROW_NUMBER() over (ORDER BY prj.ProjectName) as Row       
            , ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) / 3  as X
            ,  ((ROW_NUMBER() over (ORDER BY prj.ProjectName)) - 1) % 3  as   Y

This took a lot longer to work out that i care to admit. So hopefully someone else will find this useful

 

If you need more columns before the split then simply change the 3 to be what ever number you need.

via Buzz Blog http://paulbuzzblog.wordpress.com/2012/08/09/generate-unique-x-and-y-coordinates-or-grid-references-from-a-row-number/

Chris Stretton
Paul is a an expert SharePoint and Project Server developer and is responsible for designing and implementing custom solutions on client systems using the latest SharePoint and .NET technologies.
Paul has extensive experience with SharePoint systems across all sizes of implementation, ranging from small to large farms and has an excellent understanding of all the elements of SharePoint.

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

Advertisements
  1. Nate Auchter
    August 9, 2012 at 23:17

    Thanks Graham – I was doing something similar for a risk matrix earlier, and solved it a different way, but this would have been a HUGE shortcut. I’ll keep it in my back pocket for later…

  2. Nate Auchter
    August 10, 2012 at 13:35

    Sorry – meant Paul – must of had the crackers on my mind…

    • August 10, 2012 at 18:37

      Hi Nate,

      Glad you found it useful. I saw Paul today and he was just happy to have helped someone out.

      If you end up using it, let us know. We’d love to hear from you 🙂

      Kind Regards

      Giles

  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: