January 20, 2012 at 9:06 am
Hi there,
I'm trying to display a grid on a webpage showing location..
I need a grid, broken out like so:
12345678910
1u01u02u03u04u05u06u07u08u09u10
2u11u12u13u14u15u16u17u18u19u20
3u21u22u23u24u25u26u27u28u29u30
4u31u32u33u34u35u36u37u38u39u40
5u41u42u43u44u45u46u47u48u49u50
6u51u52u53u54u55u56u57u58u59u60
7u61u62u63u64u65u66u67u68u69u70
8u71u72u73u74u75u76u77u78u79u80
9u81u82u83u84u85u86u87u88u89u90
10u91u92u93u94u95u96u97u98u99u100
With the data in the table like this:
locidxlocyloc
u0111
u0221
uo331
..
u50105
u5115
..
u1001010
Now where this gets confusing is that I can only display a 5x5 grid at any time, and where possible, I need to keep my current location centered
so for example, if my location was xloc 4, yloc 7
I need to return:
23456
5u42u43u44u45u46
6u52u53u54u55u56
7u62u63u64u65u66
8u72u73u74u75u76
9u82u83u84u85u86
Which is centered, and works well
But if my location was xloc 1, yloc 2
12345
1u01u02u03u04u05
2u11u12u13u14u15
3u21u22u23u24u25
4u31u32u33u34u35
5u41u42u43u44u45
I can't center it as I'm running up against the edge of my locations
I'm at a loss on how to do this.. I thought maybe I could use some logic on if xloc < 3 or yloc < 3 etc.. but its getting overly complication and I can't imagine very efficient
I was hoping someone here could provide me a simpler solution?
Thanks very much
p.s - sorry about the poor formatting - not sure how I can make it better? copy/pasted into notepad tidies it up
January 20, 2012 at 9:40 am
Hi and welcome to SSC. It is nearly impossible for anybody to really understand your problem the way you have it posted. Typically what you should post is ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. It is a bit time consuming to do this but consider that the people on here are volunteers. I would guess you would rather those of us helping spend our time working a tested and fast solution to your problem instead of setting it up so we can work on code. Take a look at the first link in my signature for best practices on posting your question in a format that is most likely to generate the best help to you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2012 at 9:42 am
p.s - sorry about the poor formatting - not sure how I can make it better? copy/pasted into notepad tidies it up
Forgot to mention that to post code you can use the IFCode shortcuts. They are over on the left side when you are posting.
It lets you put code in code sections so the formatting looks nice.
this is a code section that will format code quite nicely. :D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 20, 2012 at 10:14 am
Hey there..
point taken - i thought I had enough info in there.. but rereading it.. I could have been clearer - at the bottom of my post is the create and insert scripts
What I'm looking to get is to display my results in a 5x5 grid, centered where possible, and not centered where not possible
(NOTE: locid is the value being populated in the results, all columns)
if xloc = 1 and yloc = 2 (locid = 2) - my result set would return
col1col2col3col4col5
row1111213141
row2212223242
row3313233343
row4414243444
row5515253545
Which is not centered because I'm reaching the edge of my x/y values
however, if xloc = 6 and yloc = 4 (locid = 54)
col1col2col3col4col5
row13242526272
row23343536373
row33444546474
row43545556575
row53646566676
We can see locid 54 is centered in the grid
If it was xloc = 9, yloc = 9 (locid = 89)
col1col2col3col4col5
row15666768696
row25767778797
row35868788898
row45969798999
row560708090100
again.. not centered because I reached the edge of my x/y numbers
and finally another example of it centered..
xloc = 4, yloc = 5 (locid = 35)
col1col2col3col4col5
row11323334353
row21424344454
row31525354555
row41626364656
row51727374757
Again, its centered because we don't reach the edge of the x/y numbers
Hope thats a bit clearer!
Thanks a lot!
CREATE TABLE [dbo].[location](
[locid] [int] NOT NULL,
[xloc] [int] NOT NULL,
[yloc] [int] NOT NULL,
CONSTRAINT [PK_location] PRIMARY KEY CLUSTERED
(
[locid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[location]([locid], [xloc], [yloc])
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 1, 4 UNION ALL
SELECT 5, 1, 5 UNION ALL
SELECT 6, 1, 6 UNION ALL
SELECT 7, 1, 7 UNION ALL
SELECT 8, 1, 8 UNION ALL
SELECT 9, 1, 9 UNION ALL
SELECT 10, 1, 10 UNION ALL
SELECT 11, 2, 1 UNION ALL
SELECT 12, 2, 2 UNION ALL
SELECT 13, 2, 3 UNION ALL
SELECT 14, 2, 4 UNION ALL
SELECT 15, 2, 5 UNION ALL
SELECT 16, 2, 6 UNION ALL
SELECT 17, 2, 7 UNION ALL
SELECT 18, 2, 8 UNION ALL
SELECT 19, 2, 9 UNION ALL
SELECT 20, 2, 10 UNION ALL
SELECT 21, 3, 1 UNION ALL
SELECT 22, 3, 2 UNION ALL
SELECT 23, 3, 3 UNION ALL
SELECT 24, 3, 4 UNION ALL
SELECT 25, 3, 6 UNION ALL
SELECT 26, 3, 7 UNION ALL
SELECT 27, 3, 8 UNION ALL
SELECT 28, 3, 9 UNION ALL
SELECT 29, 3, 10 UNION ALL
SELECT 30, 4, 1 UNION ALL
SELECT 31, 4, 2 UNION ALL
SELECT 32, 4, 3 UNION ALL
SELECT 33, 4, 4 UNION ALL
SELECT 34, 4, 5 UNION ALL
SELECT 35, 4, 5 UNION ALL
SELECT 36, 4, 6 UNION ALL
SELECT 37, 4, 7 UNION ALL
SELECT 38, 4, 8 UNION ALL
SELECT 39, 4, 9 UNION ALL
SELECT 40, 4, 10 UNION ALL
SELECT 41, 5, 1 UNION ALL
SELECT 42, 5, 2 UNION ALL
SELECT 43, 5, 3 UNION ALL
SELECT 44, 5, 4 UNION ALL
SELECT 45, 5, 5 UNION ALL
SELECT 46, 5, 6 UNION ALL
SELECT 47, 5, 7 UNION ALL
SELECT 48, 5, 8 UNION ALL
SELECT 49, 5, 8 UNION ALL
SELECT 50, 5, 10
COMMIT;
RAISERROR (N'[dbo].[location]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[location]([locid], [xloc], [yloc])
SELECT 51, 6, 1 UNION ALL
SELECT 52, 6, 2 UNION ALL
SELECT 53, 6, 3 UNION ALL
SELECT 54, 6, 4 UNION ALL
SELECT 55, 6, 5 UNION ALL
SELECT 56, 6, 6 UNION ALL
SELECT 57, 6, 7 UNION ALL
SELECT 58, 6, 8 UNION ALL
SELECT 59, 6, 9 UNION ALL
SELECT 60, 6, 10 UNION ALL
SELECT 61, 7, 1 UNION ALL
SELECT 62, 7, 2 UNION ALL
SELECT 63, 7, 3 UNION ALL
SELECT 64, 7, 4 UNION ALL
SELECT 65, 7, 5 UNION ALL
SELECT 66, 7, 6 UNION ALL
SELECT 67, 7, 7 UNION ALL
SELECT 68, 7, 8 UNION ALL
SELECT 69, 7, 9 UNION ALL
SELECT 70, 7, 10 UNION ALL
SELECT 71, 8, 1 UNION ALL
SELECT 72, 8, 2 UNION ALL
SELECT 73, 8, 3 UNION ALL
SELECT 74, 8, 4 UNION ALL
SELECT 75, 8, 5 UNION ALL
SELECT 76, 8, 6 UNION ALL
SELECT 77, 8, 7 UNION ALL
SELECT 78, 8, 8 UNION ALL
SELECT 79, 8, 9 UNION ALL
SELECT 80, 8, 10 UNION ALL
SELECT 81, 9, 1 UNION ALL
SELECT 82, 9, 2 UNION ALL
SELECT 83, 9, 3 UNION ALL
SELECT 84, 9, 4 UNION ALL
SELECT 85, 9, 5 UNION ALL
SELECT 86, 9, 6 UNION ALL
SELECT 87, 9, 7 UNION ALL
SELECT 88, 9, 8 UNION ALL
SELECT 89, 9, 9 UNION ALL
SELECT 90, 9, 10 UNION ALL
SELECT 91, 10, 1 UNION ALL
SELECT 92, 10, 2 UNION ALL
SELECT 93, 10, 3 UNION ALL
SELECT 94, 10, 4 UNION ALL
SELECT 95, 10, 5 UNION ALL
SELECT 96, 10, 6 UNION ALL
SELECT 97, 10, 7 UNION ALL
SELECT 98, 10, 8 UNION ALL
SELECT 99, 10, 9 UNION ALL
SELECT 100, 10, 10
COMMIT;
RAISERROR (N'[dbo].[location]: Insert Batch: 2.....Done!', 10, 1) WITH NOWAIT;
GO
January 20, 2012 at 11:17 am
I think that part of the problem is your database design. It sounds like your problem should be implemented using the geometry data type which has methods specifically to deal with polygons like a square centered around a particular point. Check out the following article on Getting Started with the geometry Data Type.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 20, 2012 at 11:38 am
While I appreciate the advise, the page you linked really didn't tell me a whole lot, except give me the indication that I'd be using a sledgehammer to kill a mosquito
It told me how to insert some data (without explaining what the data was that it was inserting) - and really doesn't give a good way of explaining how to USE it
That being said, I'm not ignoring it, I'm doing more research on it - but its slow going because I really haven't used this datatype before.
If you can give me an example or two in relation to my example that'd help a lot I think.. what I've found so far has been pretty confusing.
For a little more info. my x/y will be at most 50x50
January 20, 2012 at 12:35 pm
Is this output being dleivered through OLAP software. If so it might be better to deal with the table formatting issue in there rather than directly from the sql.
January 20, 2012 at 1:00 pm
For right now, it just needs to be returned in the SQL
January 20, 2012 at 1:25 pm
kevmck (1/20/2012)
While I appreciate the advise, the page you linked really didn't tell me a whole lot, except give me the indication that I'd be using a sledgehammer to kill a mosquitoIt told me how to insert some data (without explaining what the data was that it was inserting) - and really doesn't give a good way of explaining how to USE it
That being said, I'm not ignoring it, I'm doing more research on it - but its slow going because I really haven't used this datatype before.
If you can give me an example or two in relation to my example that'd help a lot I think.. what I've found so far has been pretty confusing.
For a little more info. my x/y will be at most 50x50
I've never used the geometry data type either. It was introduced in SQL 2008 and we're still on SQL 2005. It just seems from the description of your problem that this is the kind of situation that the geometry data type was implemented to solve.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 20, 2012 at 1:38 pm
Something along the lines of...
create table ##temp(
locid varchar(10), xloc int, yloc int)
insert into ##temp select 'u01', 1 , 2
insert into ##temp select 'u02', 1 , 3
insert into ##temp select 'u03', 1 , 4
insert into ##temp select 'u04', 1 , 5
insert into ##temp select 'u05', 1 , 6
insert into ##temp select 'u11', 2 , 2
insert into ##temp select 'u12', 2 , 3
insert into ##temp select 'u13', 2 , 4
insert into ##temp select 'u14', 2 , 5
insert into ##temp select 'u15', 2 , 6
insert into ##temp select 'u21', 3 , 2
insert into ##temp select 'u22', 3 , 3
insert into ##temp select 'u23', 3 , 4
insert into ##temp select 'u24', 3 , 5
insert into ##temp select 'u25', 3 , 6
select xloc as '1', [2],[3],[4],[5],[6] from
(select locid, xloc, yloc from ##temp) as x
pivot (max(locid) for yloc in ([2],[3],[4],[5],[6]) ) as pvt
January 20, 2012 at 2:02 pm
CELKO (1/20/2012)
>> I'm trying to display a grid on a webpage showing location.. <<NO! SQL is a data base language; all the display is done in the front end. We have ONE AND ONLY ONE data structure, the table.
"A problem well stated is a problem half solved." -- Charles F. Kettering
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
Not to be rude, because I really do appreciate those that help - but I have posted my table structure, the insert statements to populate the test data, the required output and an explanation of what it is I'm trying to do.
Your entire post seems to hinge upon the fact that you didn't read anything past the first post, or even past the part of it saying that I wanted to display data on a webpage - and if you're unable or unwilling to help, thats fine, but I think perhaps the easier approach would be not to reply.
My requirement is to provide the data from the SQL so that a grid can be populated. That grid needs to be populated in such a way that a specific column is central within the grid - I don't see what the problem is with returning the data in the correct format before it reaches the webpage as it will be significantly easier to provide the RIGHT data than to try and sort through excessive amounts of data and logic in the code. After all.. in your own words, SQL is a data language.. thats what I'm doing.. getting the data.
January 20, 2012 at 4:08 pm
How about this?
DECLARE @xloc INT ,
@yloc INT ,
@MinVerticalBoundary INT,
@MaxVerticalBoundary INT,
@MinHorizontalBoundary INT,
@MaxHorizontalBoundary INT
SET @xloc = 1
SET @yloc = 2
SELECT @MinVerticalBoundary = CASE
WHEN @yloc - 2 <= 0 THEN 1
WHEN @yloc + 2 > 10 THEN 6
ELSE @yloc - 2
END
SELECT @MaxVerticalBoundary = CASE
WHEN @yloc - 2 <= 0 THEN 5
WHEN @yloc + 2 > 10 THEN 10
ELSE @yloc + 2
END
SELECT @MinHorizontalBoundary = CASE
WHEN @xloc - 2 <= 0 THEN 1
WHEN @xloc + 2 > 10 THEN 6
ELSE @xloc - 2
END
SELECT @MaxHorizontalBoundary = CASE
WHEN @xloc - 2 <= 0 THEN 5
WHEN @xloc + 2 > 10 THEN 10
ELSE @xloc + 2
END
IF OBJECT_ID('TempDB..#Numbers') IS NOT NULL
DROP TABLE #Numbers
IF OBJECT_ID('TempDB..#Hori') IS NOT NULL
DROP TABLE #Hori
IF OBJECT_ID('TempDB..#Verti') IS NOT NULL
DROP TABLE #Verti
SELECT TOP 10 IDENTITY(int, 1,1) AS ID_Num
INTO #Numbers
FROM master.sys.columns;
-- Horizontal Boundary Numbering
SELECT RN = ROW_NUMBER() OVER ( ORDER BY ID_Num) , ID_Num
INTO #Hori
FROM #Numbers
WHERE ID_Num BETWEEN @MinHorizontalBoundary AND @MaxHorizontalBoundary
-- Vertical Boundary Numbering
SELECT RN = ROW_NUMBER() OVER ( ORDER BY ID_Num ) ,ID_Num
INTO #Verti
FROM #Numbers
WHERE ID_Num BETWEEN @MinVerticalBoundary AND @MaxVerticalBoundary
; WITH CalculatedTable AS
(
SELECT [locid], Hori.RN x_RN , Verti.RN Y_RN
FROM [location] Loc
CROSS APPLY (SELECT RN FROM #Hori WHERE #Hori.ID_Num = Loc.xloc ) Hori
CROSS APPLY (SELECT RN FROM #Verti WHERE #Verti.ID_Num = Loc.yloc ) Verti
WHERE xloc BETWEEN @MinHorizontalBoundary AND @MaxHorizontalBoundary
AND yloc BETWEEN @MinVerticalBoundary AND @MaxVerticalBoundary
)
SELECT Row = 'Row' + CAST( Y_RN AS CHAR(1)),
Col1 = [1] ,
Col2 = [2] ,
Col3 = [3] ,
Col4 = [4] ,
Col5 = [5]
FROM CalculatedTable Pivot_Table
PIVOT( MAX([locid]) FOR x_RN IN ( [1] , [2] , [3] , [4] , [5])) pivot_handle
There is a flaw in your sample data. Check the xloc and yloc values for numbers from 30 till 34.
Tell us if this worked for you
January 20, 2012 at 11:59 pm
I found the flaw in the data a little while ago.. was wondering why i kept getting a subquery returns more than 1 row of data error.
I'm out of my office till Monday, but I'll definitely give this one a try then and reply when i run it.. thank you!
January 23, 2012 at 11:21 pm
kevmck (1/20/2012)
I'm out of my office till Monday, but I'll definitely give this one a try then and reply when i run it.. thank you!
Did the query work for you?
January 24, 2012 at 1:48 pm
ColdCoffee (1/23/2012)
kevmck (1/20/2012)
I'm out of my office till Monday, but I'll definitely give this one a try then and reply when i run it.. thank you!Did the query work for you?
Hey there.. sorry for not getting back to you yesterday - was snowed under with some work from the w/end.
What you gave me ran quickly, and seems to work fantastically with my test data thanks - I'm going to build this into my bigger real world solution and see how it goes
Thanks so much
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply