none
Query Help

    Question

  • Posted - 08/29/2014 :  18:14:17  Show Profile  Edit Topic  Reply with Quote  Delete Topic  Nuke Spam!

    I have table like this

    CID    OTID OID
    30922 2426 396214
    30928 2419 551550
    30930 2419 551550
    30931 2417 551550
    30925 2401 483619
    30929 2401 483619
    30940 2401 483619

    expected result is this. basicaly its group by OID and QTID

    CID OTID OID
    30922 2426 396214
    30928,30930 2419 551550
    30931 2417 551550
    30925,30929,30940 2401 483619

    Thanks
    Friday, August 29, 2014 10:16 PM

Answers

  • One method:

    SELECT
    	STUFF((SELECT ',' + CAST(t.CID AS varchar(10))
    		FROM dbo.YourTable AS t
    		WHERE
    			t.OTID = groups.OTID
    			AND t.OID = groups.OID
    			FOR XML PATH('')
    			), 1, 1, '') AS CID
    	, groups.OTID
    	, groups.OID
    FROM (
    		SELECT DISTINCT OTID, OID
    		FROM dbo.YourTable
    	) AS groups;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by WebRelations Friday, August 29, 2014 10:41 PM
    Friday, August 29, 2014 10:34 PM

All replies

  • One method:

    SELECT
    	STUFF((SELECT ',' + CAST(t.CID AS varchar(10))
    		FROM dbo.YourTable AS t
    		WHERE
    			t.OTID = groups.OTID
    			AND t.OID = groups.OID
    			FOR XML PATH('')
    			), 1, 1, '') AS CID
    	, groups.OTID
    	, groups.OID
    FROM (
    		SELECT DISTINCT OTID, OID
    		FROM dbo.YourTable
    	) AS groups;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by WebRelations Friday, August 29, 2014 10:41 PM
    Friday, August 29, 2014 10:34 PM
  • Thanks

    Friday, August 29, 2014 10:41 PM