Home > 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.

Advertisements
Categories: work Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

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: