Tricky SQL query

[gee]

Hi

I have a table which looks like this:

Domain | Updated
abc.com 2006
cbc.net 2001
abc.com 2007
abc.com 2014
bbc.com 1997
bbc.com 2002


And need to get something like this:

Domain | Updated
abc.com 2006,2007,2014
cbc.net 2001
bbc.com 1997,2002

or even better if domain that appears only one in DB would have no value "Updated" at all:
Domain | Updated
abc.com 2006,2007,2014
cbc.net
bbc.com 1997,2002


IS IT ACTUALLY POSSIBLE TO ACHIEVE? ANY IDEAS?

 

[Peter]

Don't see any reason why it would not be possible. I take it you want it all returned as a string so each site only has 1 row.

Not currently in a position to test but should be able to conjure something up

 

[Dominic Belley]

This should do the trick for you.

You did not mention which DB engine you are using, my code is SQL Server. Of course if you use MySQL or Oracle, this won't work.

SELECT DISTINCT DY1.Domain,
  STUFF((SELECT ',' + CAST(DY2.Updated AS VARCHAR(5))
         FROM DomainYear DY2
         WHERE DY1.Domain = DY2.Domain
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)')
        ,1,1,'') AS YearList
FROM DomainYear DY1

 

[Dominic Belley]

This one is a bit messy, but it removes the year when it has only one.
I did not put any thought into it. Just added a CASE to see if LEN of yearlist is 4.

SELECT DISTINCT DY1.Domain,
    CASE
        WHEN LEN(STUFF((SELECT ',' + CAST(DY2.Updated AS VARCHAR(5))
                         FROM DomainYear DY2
                         WHERE DY1.Domain = DY2.Domain
                            FOR XML PATH(''), TYPE
                            ).value('.', 'VARCHAR(MAX)')
                        ,1,1,'')) = 4 THEN ''
        ELSE
              STUFF((SELECT ',' + CAST(DY2.Updated AS VARCHAR(5))
                     FROM DomainYear DY2
                     WHERE DY1.Domain = DY2.Domain
                        FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)')
                    ,1,1,'')
    END AS YearList
FROM DomainYear DY1