Recreating MySQL GROUP_CONCAT In MSSQL (Cross Tab Query)

As a DBA (Database Administrator) for both MySQL and MSSQL databases, I often find myself required to write queries and reports that need a Cross-Tab Query. Cross -Tab queries are something that MSAccess supports, but so little other database do. MySQL 5 saw the introduction of the wonderful GROUP_CONCAT function that allows you to do the job, but MSSQL still does not have support, at least not in a straightforward manner. In this article, I will show you a simple example of how to recreate a crosstab query in MSSQL using a CURSOR.

Cross tab queries are used when you have a table that has results like.

Person	Colour
Steve	Blue
Steve	Red
Dave	Red
Dave	Red

And you wish to create a result set looking like this

Person	Colours
Steve	Blue,Red
Dave	Red,Red

I.E: You wish to group the results by one column, with the 2nd column showing each of the results as a comma separated value.

Here's how you can do it, using MSSQL Cursors.

BEGIN
-- Start the cursor
DECLARE querycur CURSOR
FOR
 SELECT
     T.ABTA, R.Scheme
 FROM
   TravelAgent AS T
  LEFT JOIN A_SchemeRegister AS R ON R.ABTA = T.ABTA
   WHERE
  T.ABTA = '00001'
OPEN querycur --open the cursor

-- Declare Local Vars
DECLARE @ABTA varchar(5)
DECLARE @Scheme varchar(6)

-- Create temp table
CREATE TABLE #Result (
 ABTA VARCHAR(5) NOT NULL,
 Schemes VARCHAR(8000) NULL
)


-- Load the first row into the cursor
FETCH NEXT FROM querycur INTO @ABTA, @Scheme

WHILE (@@FETCH_STATUS <> -1) -- check for final row
BEGIN
 IF (@@FETCH_STATUS <> -2) -- check for error
  BEGIN

  IF EXISTS(SELECT * FROM #Result WHERE ABTA = @ABTA)
  BEGIN
    -- Row Exists so append result
    UPDATE #Result
    SET
      Schemes = Schemes+','+@Scheme
    WHERE 
      ABTA = @ABTA
  END
  ELSE
  BEGIN
    -- Row didn't exist so new record
    INSERT INTO #Result
    ( ABTA, Schemes )
    VALUES
    ( @ABTA, @Scheme )

  END
 END
-- Load the next row into the cursor
FETCH NEXT FROM querycur INTO @ABTA, @Scheme
END

CLOSE querycur
DEALLOCATE querycur

-- Show results
SELECT * FROM #Result

-- Clean up
DROP TABLE #Result

END

---