Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RowsAndColumns
Starting Member
6 Posts |
 Posted - 2009-11-23 : 23:05:03
|
I don't know if this is possible just using GroupBy, buy I thought I'll ask.I have a query/view that pulls data from various tables.This is what it ends up looking like: (Hopefully the columns line up.)Project Tile SubProj SubTitle DataFile DataTitle Who Group------- ------ ------ ------- -------- --------- ------ -------133 Int 2149 Exit 342 Recovery Keith Manager 133 Int 2149 Exit 379 Access Fred Manager 132 Rem 2029 Rem 396 TEST DATA Peter Control132 Rem 2029 Rem 396 TEST DATA Mark Manager This is what I want:Project Tile SubProj SubTitle DataFile DataTitle Who Group------- ------ ------ ------- -------- --------- ------ ------133 Int 2149 Exit 342 Recovery Keith Manager 379 Access Fred Manager 132 Rem 2029 Rem 396 TEST DATA Peter Control 396 TEST DATA Mark Manager ie. Where the data from 'Project' and/or 'SubProj.' are the same, don't populate the row.I thought this would be easy, and I would just need to use the GroupBy function, but alas, I was wrong!As this is a very simplified version of the actual output, it's hard for me to include the SQL code.This would be the SELECT part:SELECT TOP (100) PERCENT dbo.Project_tbl.Proj_No AS [Project], dbo.Project_tbl.Title AS [Title], dbo.SubProj_tbl.SubProj_No As [SubProj], dbo.SubProj_tbl.Title AS [SubTitle], dbo.DataFile_tbl.DataFile_Number AS [DataFile], dbo.DataFile_tbl.Title AS [DataTitle], dbo.Staff_Member_tbl.Name AS [Who], dbo.Staff_Groups_tbl.GroupThe FROM is a bit harder. I'll try to explain it:The Project_tbl would give me the SubProject Numbers. I would then use these numbers in the SubProj_tbl to get the DataFile Numbers. I would then get the DataFile Title from the DataFile_tbl using these numbers. The WHERE would be a simple 'dbo.Project_tbl.Completed = No' .Any help would be appreciated.I've tried to include as much info as possible.Thanx!!(Using worked a treat. The columns now line up.) |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-24 : 17:07:58
|
| >>(It seems the columns are not lining up properly when posted, but they look OK when I edit the post.try enclosing your formated example as well as your code in code tags:[code] [/code]I can't tell what you need. Why don't you post some DDL (create table statements), DML (insert statements for sample data), then post your expected output based on the sample data you provide.Be One with the OptimizerTG |
 |
|
|
RowsAndColumns
Starting Member
6 Posts |
Posted - 2009-11-24 : 17:19:53
|
Thanks for that.  The columns now line up properly, and you can see what I'm after. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-25 : 11:39:41
|
This conditional presentation thing is really a report-formatting issue. Are you presenting this data in some reporting tool like Crystal Reports? If so you should use their grouping functionality for this.If you have your heart set on doing this in sql you would have to do something like this. Run this code in any db to see the effect: (assumes you have sql 2005 or later)select case when rn = 1 then table_name else '' end as table_name ,column_namefrom ( select table_name ,column_name ,row_number() over (partition by table_name order by ordinal_position) as rn ,table_name as tableSort from information_schema.columns ) dorder by tableSort, rn Be One with the OptimizerTG |
|
|
|
RowsAndColumns
Starting Member
6 Posts |
Posted - 2009-11-25 : 18:43:09
|
You are spot on.I came to the same conclusion late yesterday.Thanks for the help!quote:
Originally posted by TG
This conditional presentation thing is really a report-formatting issue. Are you presenting this data in some reporting tool like Crystal Reports? If so you should use their grouping functionality for this.Be One with the OptimizerTG
Thanx!! |
|
|
|
|
|
|
|
|
Do I use GroupBy, or Something Else?