Help with simple SELECT query - 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 2008 Forums

Transact-SQL (2008)

Help with simple SELECT query

Author

Topic

madlan
Starting Member

5 Posts

Posted - 2012-03-05 : 14:32:21

Hi all,

My table is in the following format:

Group ID
Red 1
Red 2
Yellow 3
Yellow 4
Yellow 5
Green 6
Blue 7

And I want to return it like this:

Group IDs
Red 1,2
Yellow 3,4,5
Green 6
Blue 7

So far I've got the following but not sure how to go any further!

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + cast(ID as VARCHAR)
FROM mytable
WHERE group = 'Yellow'
SELECT DISTINCT group, @listStr AS IDs FROM mytable
WHERE group = 'Yellow'

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-05 : 14:49:46

Here's one way


SELECT
    m.[g] AS [Group]
    , LEFT(p.[IDS], LEN(p.[IDS]) - 1) AS [IDs]
FROM
    (
    SELECT DISTINCT [Group] AS [g] FROM myTable
    )
    AS m

    CROSS APPLY (
        SELECT CAST([ID] AS VARCHAR(10)) + ','
        FROM myTable AS m2
        WHERE m2.[Group] = m.[g]
        ORDER BY m2.[ID]
        FOR XML PATH('')
        )
        AS p ([IDS])

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-05 : 14:50:03

group is a reserved keyword btw

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

madlan
Starting Member

5 Posts

Posted - 2012-03-05 : 16:37:18

Hi Charlie,

Thanks for that, I was trying to avoid using the cross apply (Not supported by VB.net), sorry, I should have mentioned that in the first post

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources