September 2, 2009 at 11:42 am
[font="Times New Roman"][font="Arial"]Hello,
i need help, i am a newbee in T-SQL and don't know much about T-SQL in depth.
Assume a "SELECT CHAPTERS, ARTICLES FROM MyTable ORDER BY CHAPTERS, ARTICLES" gives you that:
#temptable1
CHAPTERS ARTICLES
Chapter 1 Article 1
Chapter 1 Article 4
Chapter 1 Article 2
Chapter 2 Article 1
Chapter 3 Article 5
Chapter 3 Article 7
I'd like to modify a that table above to a table like that:
#temptable2
CHAPTERS ARTICLES
Chapter 1 Article 1
./. Article 4
./. Article 2
Chapter 2 Article 1
Chapter 3 Article 5
./. Article 7
Is that possible with T-SQL in a stored proecdure ?
Thank you in advance.
Sascha[/font][/font]
September 2, 2009 at 11:46 am
Looks like what you're aiming at is a slight variation on a pretty normal tree-view.
Usually, the way you generate those, is have an XML query that produces the data you want, and let the front-end app format it to the way you want it to look. Gives you more options, and you can do more with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 12:08 pm
Keep in mind also that the XML structure has something that the table does not.... an implicit, predictable order.
You do not want not do that to your permanent since there is no "natural order" in a table. Tables, per RDBMS theory are unordered sets, so you have no guarantee the rows will keep coming back in the same order without using a sort based on data available to the row at that time.(ORDER BY in T-SQL).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 2, 2009 at 1:09 pm
Thank you all for your fast response, but i dont think that XML thing will help me. My example is abstract, the real Table is much more complex. Please look again at the image i attached to the post.
I need to know how to manipulate each dataset one step after another, in a loop, with IFs/ELSEs, compare values of the datasets before with the curret one. Is that possible with T-SQL in a stored procedure ? I am a newbee in T-SQL and stored procedure writing.
It would be great if you can provide me with a simple example.
I need to rewrite that chapter table, because this table will be visualised in an ASP DataGrid in an .asmx File. This asmx File will be deployed as a user control via "Return Of the Smartpart" at a MS Sharepoint Server.
This table I'd like to rewrite has some inline JavaScript in it and hrefs, mailtos and an special HTML CTI-Tags for IP Phones.
Id like keep the formating of that in a stored procedure, i dont wanna get it to the guys that do the Asp and MOSS thing. The Datagrid should just simple display that table.
Greetings Sascha
September 2, 2009 at 1:20 pm
As montioned there is no order in a set, so I added an IDENTITY column to mainatain that order. In the real world you would have some sort of order column to use unless you can order by the article name.DECLARE @temptable1 TABLE (ID INT IDENTITY(1,1), Chapters VARCHAR(100), Articles VARCHAR(100))
INSERT @TempTable1
SELECT 'Chapter 1', 'Article 1'
UNION ALL SELECT 'Chapter 1', 'Article 4'
UNION ALL SELECT 'Chapter 1', 'Article 2'
UNION ALL SELECT 'Chapter 2', 'Article 1'
UNION ALL SELECT 'Chapter 3', 'Article 5'
UNION ALL SELECT 'Chapter 3', 'Article 7'
SELECT
CASE
WHEN RowNum = 1 THEN Chapters
ELSE './.'
END AS Chapters,
Articles
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Chapters ORDER BY Chapters, ID) AS RowNum
FROM
@TempTable1
) AS T
September 2, 2009 at 1:25 pm
Heh... ok... everyone always leaves the hard questions up to me so I'll break the ice on the hard question... 😉
Why are we using SQL Server like a word processor or is this just an example for a desired tree view?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2009 at 1:39 pm
Jeff Moden (9/2/2009)
Heh... ok... everyone always leaves the hard questions up to me so I'll break the ice on the hard question... 😉Why are we using SQL Server like a word processor or is this just an example for a desired tree view?
Actually Jeff, my original response was to leave that kind of thing up to the front end. That's the same thing you're asking.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 1:45 pm
sascha (9/2/2009)
Thank you all for your fast response, but i dont think that XML thing will help me. My example is abstract, the real Table is much more complex. Please look again at the image i attached to the post.I need to know how to manipulate each dataset one step after another, in a loop, with IFs/ELSEs, compare values of the datasets before with the curret one. Is that possible with T-SQL in a stored procedure ? I am a newbee in T-SQL and stored procedure writing.
It would be great if you can provide me with a simple example.
I need to rewrite that chapter table, because this table will be visualised in an ASP DataGrid in an .asmx File. This asmx File will be deployed as a user control via "Return Of the Smartpart" at a MS Sharepoint Server.
This table I'd like to rewrite has some inline JavaScript in it and hrefs, mailtos and an special HTML CTI-Tags for IP Phones.
Id like keep the formating of that in a stored procedure, i dont wanna get it to the guys that do the Asp and MOSS thing. The Datagrid should just simple display that table.
Greetings Sascha
Yes, you can use a cursor to step through each row of a dataset (the result of any valid select statement) and do various actions on each row.
The problem is that it's a really bad idea, doesn't work very well, isn't what the database is designed for, and is generally much harder to implement and maintain than other solutions outside the database.
That's why we're asking you if it can be done outside the database. If it's possible (and in my experience it always is), it'll save you a lot of time, effort, and headaches.
If you really want to do this in the database, despite all recommendations to the contrary, look up "declare cursor" in Books Online (or MSDN). That'll tell you how to build a cursor and how to use it to step through the data one row at a time. Don't be surprised if it's slow, buggy, and/or hard to maintain.
It's like swimming with only your hands, no kicking. It can be done, but it's slow, takes more effort, and is more likely to drown you. Possible is not equal to smart.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 2, 2009 at 2:45 pm
As montioned there is no order in a set, so I added an IDENTITY column to mainatain that order. In the real world you would have some sort of order column to use unless you can order by the article name.
Thank you Lamprey13 for that example, i'll try to get it done with that.
Why are we using SQL Server like a word processor or is this just an example for a desired tree view?
--Jeff Moden
It's like swimming with only your hands, no kicking. It can be done, but it's slow, takes more effort, and is more likely to drown you. Possible is not equal to smart.
- GSquared
Yes Jeff and GSquared, your right its not the smartest solution, to missuse the SQLSever as a "word processor" .
But this time I want to deliver fully formated data to that ASP.NET Datagrid. I dont want to do that stuff in the ASP.NET Code that time. The amount of data I am speaking of is round about 400 datasets not more. I think the SQL Server should handle that, even with "declare cursor" thing (which i will take a look too).
Thank you again all for your fast help, ill try it tomorrow at work.
Good night everyone.
Sascha
September 2, 2009 at 3:18 pm
sascha (9/2/2009)
Yes Jeff and GSquared, your right its not the smartest solution, to missuse the SQLSever as a "word processor" .But this time I want to deliver fully formated data to that ASP.NET Datagrid. I dont want to do that stuff in the ASP.NET Code that time. The amount of data I am speaking of is round about 400 datasets not more. I think the SQL Server should handle that, even with "declare cursor" thing (which i will take a look too).
Those are all the exact reasons why you shouldn't do it in T-SQL. You don't know how to do it which also means that you really wouldn't be able to support it and now you're turning to one of the 2nd worst things you can do to a server... write a formatting cursor.
You don't need a cursor for this... do the same thing they suggested for the 2k5 solution with ROW_NUMBER except base it off of when the Article number = 1. You can do it... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2009 at 3:06 am
Thank you Lamprey13m.
I Modified your example, put that into a stored procedure and it works perfectly.
Greetings Sascha
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply