Group SQL Data - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group SQL Data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-15 : 07:55:43

Oren Levy writes "Hi All

I have a SQL statement that returns 3 values: ItemNumber, Product Line Number, Product Line Description.

(
SELECT dbo.IM1_InventoryMasterfile.ItemNumber as ItemNumber, dbo.IM1_InventoryMasterfile.ProductLine as PL, dbo.IMA_ProductLine.ProductLineDescription as PLDescription
FROM dbo.IM1_InventoryMasterfile INNER JOIN
dbo.IMA_ProductLine ON dbo.IM1_InventoryMasterfile.ProductLine = dbo.IMA_ProductLine.ProductLineCode LEFT OUTER JOIN
dbo.IM_90_UDF_IMMasterfile ON dbo.IM1_InventoryMasterfile.ItemNumber = dbo.IM_90_UDF_IMMasterfile.ItemNumber
WHERE (dbo.IM_90_UDF_IMMasterfile.Web_Exclude <> 'Y' or dbo.IM_90_UDF_IMMasterfile.Web_Exclude is null)

union

SELECT dbo.Items.ItemNumber, dbo.Items.PL, dbo.IMA_ProductLine.ProductLineDescription
FROM dbo.Items INNER JOIN
dbo.IMA_ProductLine ON dbo.Items.PL = dbo.IMA_ProductLine.ProductLineCode
)
Order By PLDescription, ItemNumber ASC

The data it returns looks like this:

ItemNumber1, PL001, PLDescription1
ItemNumber2, PL001, PLDescription1
ItemNumber3, PL001, PLDescription1
ItemNumber4, PL001, PLDescription1
ItemNumber5, PL002, PLDescription2
ItemNumber6, PL003, PLDescription2

Is there a way to group the data so it will return the data like this:

PL001, PLDescription1
ItemNumber1
ItemNumber2
ItemNumber3
ItemNumber4
PL002, PLDescription3
ItemNumber5
ItemNumber6

Thanks

Oren Levy"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-15 : 09:34:56

That is a presentation issue, not a data issue. Your presentation layer (reporting tool, web page, windows form app, etc) should be handling the formatting of the data.

In a reporting tool such as Crystal, Reporting Services, or Access, you simply create GROUPS and set up group headers and footers that handle this. For a web page, you need to do some coding to handle the formatting.

Overall, your data is fine -- you've got SQL server doing its job, returning a valid dataset for what you want, now you just need to have your presentation layer take it from here.

- Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 10:33:47

1 This is nothing but suppressing duplicate values
2 If you use reports like Crystal Reports, format the field and make use of suppress if duplicated feature
3 I am sure you want to show in reports

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources