Data Display with Query Results Question

[sue0912]

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

Sue

 

[SQLSister]

No you can't do this in SQL. If you want to display like that you will need to have your application do it.

"NOTHING is more important in a database than integrity." ESquared

 

[genomon]

For what it's worth, you can do this from a VB or Access app (and probably any flavor of C) using the MSDataShape.1 provider. If you are interested, Google that, along with the SQL keywords "SHAPE", "APPEND", and "RELATE".

Cogito eggo sum – I think, therefore I am a waffle.