Combining unique records into one

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)

Combining unique records into one

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2008-03-05 : 14:08:44

Could some one please help me with the following query.

I have multiple tables link together base on wo_No and prt_Mark fields and I get the following results.
wo_No | prt_Mark | dwg_Seq |
324037 | d400 | 1S2 |
324037 | d400 | A1 |
324037 | d400 | 1 |
219001 | 56 | 2B |
219001 | 56 | 2C |
219001 | 56 | 2C |

What I would like is to combine the unique dwg_Seq where they have the same wo_No and prt_Mark.
324037 | d400 | 1S2,A1,1 |
219001 | 56 | 2B,2C |

I would also be happy with the following results get the Max unique dwg_Seq and count how many more unique dwg_Seq there are.
324037 | d400 | A1 + 2 |
219001 | 56 | 2B + 1 |

Any help would be greatly appreciated.

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-05 : 16:35:49

Try this...

DECLARE @Table1 TABLE(wo_No int, prt_Mark varchar(20), dwg_Seq varchar(20))

INSERT @Table1
SELECT 324037, 'd400', '1S2' UNION ALL
SELECT 324037, 'd400', 'A1' UNION ALL
SELECT 324037, 'd400', '1' UNION ALL
SELECT 219001, '56', '2B' UNION ALL
SELECT 219001, '56', '2C' UNION ALL
SELECT 219001, '56', '2C'

SELECT wo_No, prt_Mark, convert(varchar,max(dwg_Seq)) + ' + ' + convert(varchar,count(distinct dwg_Seq) - 1 )
FROM @Table1
GROUP BY wo_No, prt_Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:07:10

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources