CODEWITHSUNDEEP
sqlsql-server
DarkW1nter
DarkW1nter

Reputation: 2851

selecting rows with near identical data, apart from 1 field

If I have several rows in a SQL db (access front end) with near exact data apart from one field (i know about normalization but Im happy to say this wasn't my doing!), is there an easy way to select one row with the common data and show all values for the other field together? I can write a function do it programatically but Im wodndering of there's an easier way?

eg

animal   brown   dog

animal   brown   cat

animal   brown   horse

as

animal   brown   dog, cat, horse

Upvotes: 2

Views: 110

Answers (2)

Taryn
Taryn

Reputation: 247640

You can use STUFF() to get this:

select distinct col1, col2,
  Stuff((SELECT N', ' + col3 
         FROM yourtable t2
         where t1.col1 = t2.col1
         FOR XML PATH(''),TYPE)
        .value('text()[1]','nvarchar(max)'),1,2,N'')
from yourtable t1

see SQL Fiddle with Demo

Upvotes: 3

HLGEM
HLGEM

Reputation: 96552

Here is an example of the technique you need to use:

select 'test' as Test, 1 as Item 
into #test 
union select 'test2', 2 
union select 'test', 3 
union select NUll, 4 
union select 'test', 5 

select t2.test, STUFF((SELECT  ', ' + cast(t1.Item as varchar (10) )
        FROM #test t1 where t2.test = t1.test 
        FOR XML PATH('')), 1, 1, '') 
     from #test t2
     group by t2.test

Upvotes: 1

Related Questions