Column Consolidation - 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 2005 Forums

Transact-SQL (2005)

Column Consolidation

Author

Topic

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-18 : 09:40:55

Hi,

I'm not sure if this is possible or not, because I haven't been able to figure out how to do this.

Here is the output I am currently getting.

Part Count
---- -----
Cooling System 172
Cooling System XL 159
Rosewill Cool 100
Xigmatek XLF 120
Xigmatek LED 197

I'm trying to get the following results.

Part Count
---- -----
Cooling System 331
Rosewill Cool 100
Xigmatek 317

Is it possible to achieve this, is so how?

Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 10:48:06

Hello jhorten2011,

Do you have anything with which to reference the parts by? How much variance is expected in parts?

For instance 'Cooling System'. Could it be:

'Cooling Sys'
'Cooling System'
'Cooling'

This could be easier if there was a master parts table which could be referenced. Otherwise it becomes difficult to know how granular to aggregate and try to fuzzy match parts. IMHO.

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-18 : 11:01:32

Hi,

There are instances of 'Cooling System XL' and 'Cooling System', so I would need to return just 'Cooling System', which would show a combined Count from both 'Cooling System XL' and 'Cooling System'.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 11:13:54

normalisation!

do you have a parts table? Somewhere where each part only appears once and everything else joins to this with a key? PartID or whatever?

if you do then you can simply add either a new column or a lookup table that maps those parts to the report display

Then join to that table and group on that description to get the information you want.

Does that make sense?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-18 : 11:36:24

Yes.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 11:38:44

excellent. glad to help

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources