Data Display with Query Results Question

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

Transact-SQL (2000)

Data Display with Query Results Question

Author

Topic

shigman
Starting Member

14 Posts

Posted - 2009-08-13 : 09:18:52

Good Morning,

Is there a way in the following query that can my results to display a bit differently?

My current display/output

Customer Lastofdivision GL_Accnt Prod_Line 2 3 4 5 6
PATRIOT HAULING 06 1000 SR 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 1300 Cone 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 26 X 44 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 HP 200 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 HP300 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 Lippmann 30 x 48 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 Lippmann 30 x 62 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 Lippmann 5165 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 Phoenix 2100 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 Telsmith 8 x 20 screen 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 XA400 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
PATRIOT HAULING 06 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88

Desired Display/Output

Customer Lastofdivision GL_Accnt Prod_Line 2 3 4 5 6
PATRIOT HAULING 06 1000 SR 30300-50-100-000 ADPT 2 16.3725 8.185 59.32 31.88
1300 Cone
26 X 44
HP 200
HP300
Lippmann 30 x 48
Lippmann 30 x 62
Lippmann 5165
Phoenix 2100
Telsmith 8 x 20 screen
XA400

My query

SELECT Parts_GL_TableF.Customer, Parts_GL_TableF.Lastofdivision, LEFT(replace(b.itemdescription, 'S/N', 'SN:'), charindex('SN:', replace(b.itemdescription, 'S/N', 'SN:') + 'SN:') - 1) , Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line,
SUM(Parts_GL_TableF.Qty) , SUM(Parts_GL_TableF.Unit_Cost) , SUM(Parts_GL_TableF.Ext_Cost) , SUM(Parts_GL_TableF.Unit_Price) , SUM(Parts_GL_TableF.Ext_Amnt)
FROM Parts_GL_TableF LEFT OUTER JOIN (SELECT Customer, ItemDescription FROM ED.dbo.Equip_List_F GROUP BY Customer, ItemDescription) B ON
Parts_GL_TableF.Customer = B.Customer WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS',
'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG',
'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE',
'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW',
'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI',
'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO',
'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS',
'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S')
GROUP BY Parts_GL_TableF.Lastofdivision, Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Customer, b.ItemDescription
ORDER BY Parts_GL_TableF.Lastofdivision, Parts_GL_TableF.Customer, Parts_GL_TableF.Prod_Line

The reason why I ask the question is I currently take this data and have create a pivot table. The problem arises with the totals and subtotals as the data is duplicated so my totals are off. So I am coming back to my query to see if I can output my data differently to get the correct data.

Sue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources