Complicated SQL Query
-
Hello,
I have to produce a report from a table that is extracted from a non-relational database system.
A sample of the table records would look like this:
I need to generate a single recordset that looks like this:
OR the grouped data can be combined in a single cell like this:
Does anyone know of a SQL Query or other method I may use to accomplish this?
Any help would be greatly appreciated. Thanks!
id='Arial'>id=size2>
-
I'm not saying that this is the best way of doing it but here's an idea.
create table #output
(file_name varchar(25), ref_no varchar(25), container_no varchar(25) )
declare @file_no varchar(25), @ref_no varchar(25), @container_no varchar(25)
, @prev_file_no varchar(25), @prev_ref_no varchar(25), @prev_container_no varchar(25)
, @output_file_no varchar(25), @output_ref_no varchar(25), @output_container_no varchar(25)
set @prev_file_no = ''
set @prev_ref_no = ''
set @prev_container_no = ''
declare output_cursor cursor for
select file_no, ref_no, container_no
from <table>
order by file_no, ref_no, container_no
open output_cursor
fetch next from output_cursor into @file_no, @ref_no, @container_no
while @@fetch_status = 0 begin
if @prev_file_no = @file_no set @output_file_no = ''
else
set @output_file_no = @file_no
set @prev_file_no = @file_no
/* repeat for the other fields */
insert #ouput
select @output_file_no, @output_ref_no, @output_container_no
fetch next from output_cursor into @file_no, @ref_no, @container_no
end
close output_cursor
deallocate output_cursor
select file_no, ref_no, container_no
from #output
drop table #output
Basically, it keeps the previous value in a variable and if the current value is the same as the previous then it makes the output value blank.
If you can have the same continer_no in different file_no or ref_no then you would need to reset the value of @previous_container_no so that it outputs the value.
It's not pretty but it should work.
As it uses a cursor it might not be great for a very large set of data but you could get around that by inserting the rows into a temp table with an indexed indentity field and then loop from 1 to @@identity and select the row for the loop value.
HTH
Jeremy
-
This seems to be more of a presentation question. Can you just have the interface perform the data hiding? That way the repeated entries can be hidden - but reprinted if you start a new page, etc.
Guess I don't understand how this would apply except in a presentation layer.
Guarddata-
-
For the most part, you are right. This is mainly for presentation purposes. We do have Crystal Reports, however, due to the nature of the application, we do not want to use it. It would be far too costly.
I was hoping to get the resultset from a single SQL query or Stored Procedure so that I could write the output myself.
Keep in mind that the example I offered is also very simplified.
In the real world scenario, there will be many file_no, each with its own set of ref_no and container_no.
Any other ideas? I am keeping Jeremy's as one, although it really will be a lot of data, so I am concerned about performance.
Thanks.
Bob
-
quote:
For the most part, you are right. This is mainly for presentation purposes. We do have Crystal Reports, however, due to the nature of the application, we do not want to use it. It would be far too costly.I was hoping to get the resultset from a single SQL query or Stored Procedure so that I could write the output myself.
Keep in mind that the example I offered is also very simplified.
In the real world scenario, there will be many file_no, each with its own set of ref_no and container_no.
Any other ideas? I am keeping Jeremy's as one, although it really will be a lot of data, so I am concerned about performance.
Thanks.
Bob
Contrary to your first post, the first sample you show is absolutely typical of of a result set from a normalized relational database. You're obviously dealing with one-to-many relations, and, in a relational database, you would have tables for Files, Refs, and Containers. So, if you were to ask for the data, the result set would be identical to what you show.
Removing the redundant data should indeed be done at the presentation layer; if you don't want to use your report writer, create the formatting using whatever front-end you're referring to when you write "I could write the output myself." This would be trivial in any front-end language.
--Jonathan
Edited by - jonathan on 09/25/2003 10:22:16 AM
--Jonathan -
Alternative to using cursor using temp table but depends on volume.
create table #temp (rowid int IDENTITY(1,1),file_no varchar(6), ref_no varchar(6), container_no varchar(7))
insert into #temp (file_no,ref_no,container_no)
select file_no,ref_no,container_no from [tablea]
order by file_no,ref_no,container_no
update t
set t.file_no = (case
when a.file_no is null then t.file_no
when a.file_no = t.file_no then ''
else t.file_no
end),
t.ref_no = (case
when a.ref_no is null then t.ref_no
when a.ref_no = t.ref_no then ''
else t.ref_no
end)
from #temp t
left outer join #temp a on a.rowid = t.rowid-1
select * from #temp order by rowid
Far away is close at hand in the images of elsewhere.
Anon. -
Same idea as Jeremy, but without using cursors.
Step 1 : Insert all the data in a (temporary) table that has an identity column. Make sure the data is sorted correctly.
#temptable
tid file_no ref_no container_no
1 123456 ref321 cont777
2 123456 ref456 cont555
3 123456 ref456 cont333Step 2 : Update all the 'duplicate' values to NULL (or a single space).
This query should work for file_no
UPDATE t1
SET t1.file_no = NULL
FROM #temptable t1
INNER JOIN
#temptable t2
ON t1.file_no = t2.file_no
AND t1.tid = t2.tid + 1Repeat this query (sort of) for ref_no.
Step 3: Select everything from the temp table, sorted on 'tid'.
Damn, David beat me too it
. Good to sea an alternative solution though...Edited by - NPeeters on 09/26/2003 03:22:38 AM
-
Thank you all for your suggestions. After thinking about it a while, I realized that Jonathan and some others were correct. This is truthfully a presentation issue that could just as easily be taken care of in my front-end code.
So to share with everyone, here is what I ended up doing (ASP.NET/VB.NET):
Dim my variables...
Dim cmd As SqlCommand
Dim rd As SqlDataReader
Dim strSQL As String
Dim dset As DataSet
Dim dtbl As DataTable
Dim dcol As DataColumn
Dim drow As DataRowFirst I created a new recordset...
Dim dset As New DataSetThen I create a new datatable...
Dim dtbl As New DataTable("rpt_emailstatus")Then I add the columns to my new datatable...
dcol = New DataColumn("file_no", GetType(Integer))
dtbl.Columns.Add(dcol)
dcol = New DataColumn("ref_no", GetType(String))
dtbl.Columns.Add(dcol)
dcol = New DataColumn("conainer_no", GetType(String))
dtbl.Columns.Add(dcol)I open my connection to my SQL Server and run my query...
conn.Open()
strSQL = "SELECT file_no, ref_no, container_no FROM myTable ORDER BY file_no"
cmd = New SqlCommand(strSQL, conn)I populate my datareader...
rd = cmd.ExecuteReader()I need a variable to keep track of the last file_no added and I also need a counter to make sure that I am not inserting a row when there isn't one...
Dim lastfile As Integer, i
i = 0Now, I loop through the recordset and build the rows in my dataset. If the lastfile matches the current file_no field, then I simply add the ref_no and container_no to their current values. If it is different then I close the previous row and start a new one with the new file_no...
While rd.Read()
If lastfile <> rd("file_no") Then
If i > 0 Then
dtbl.Rows.Add(drow)
End If
drow = dtbl.NewRow
drow("file_no") = rd("file_no")
drow("ref_no") = rd("ref_no")
drow("container_no") = rd("container_no")
Else
drow("ref_no") = (drow("ref_no") & "<br>" & rd("ref_no"))
drow("container_no") = (drow("container_no") & "<br>" & _
rd("container_no"))
End If
lastfile = rd("file_no")
i += 1
End While
rd.Close()
conn.Close()I add the datatable I just created to my dataset and then I bind the dataset to a datagrid that I have created...
dset.Tables.Add(dtbl)
With dgrdReport
.DataSource = dset
.DataBind()
End WithAnd that's pretty much it. It works like a charm. The nice thing is that with the dataset I have a disconnected set of data that I can then allow the user to sort and manipulate it anyway that they want to, so it meets the performance goal I was looking for. That was important because in the real-life app, there are about 20 columns and could be as many as 2000 rows for one report. (Of course for that many I will obviously page the results).
Thanks again for all of your help.
-
You mention cost as a factor of not using Crystal Reports. Do all of the users have Microsoft Office? You could use Access as the front end (best choice) or even Excel if the number of rows are not significant.
I don't know that I would suggest allowing a group of users to have an open query platform like query analyzer. You really need some other type of presentation platform.
For ease of access, I usually do reports in ASP on IIS -- Universally accessible from any client with a browser. If you have IIS available, let me know and I will give you a sample asp page that you can modify.
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC. -
John,
Thanks for the offer.
Actually, we have such a huge amount of data that we use SQL Server 2000 for all of our data needs.
I had never intended to give users access to the db with query analyzer just because these are not employees, but rather customers that would use this app.
So I built an ASP.NET front end and used SQL as my back end.
Thanks again for the suggestions.
Bob Gibilaro
-
Oops. I see you posted while I was getting the ASP ready. Great job!
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC. -
Joe,
LOL. No prob. Thanks again.
Bob
. Good to sea an alternative solution though...Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply