-1

Here is how my data looks currently

1  Inspection charge
1  Liftgate @ pickup
2  Inspection charge
2  Inside delivery
2  Inside pickup

I would like the data to look like below:

1  Inspection charge, Liftgate @ pickup
2  Inspection charge, Inside delivery, Inside pick up

The data is now showing up as a table, pls help. Also, XML Path does not work in my version of sql server.

Thanks.

Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
Ann
  • 51
  • 1
  • 7
  • 1
    You're using SQL Server 2008? What does "does not work" mean? – Aaron Bertrand Jan 05 '15 at 18:09
  • `for Xml` will work from `Sql server 2005`. You can use it in `Sql server 2008`. Check here for more info http://msdn.microsoft.com/en-IN/library/ms178107.aspx – Pரதீப் Jan 05 '15 at 18:17
  • DON'T **EVER** PUT CSV DATA IN COLUMNS! If you need to format the results of a query differnetly, handle that in the client app. – Joel Coehoorn Jan 05 '15 at 18:24
  • @JoelCoehoorn While you're right, I'm guessing that they want to select the data in a comma-separated list which is what `FOR XML` can be used for. – Taryn Jan 05 '15 at 18:25
  • See this SA question on exporting to CSV: https://stackoverflow.com/questions/7382846/exporting-data-from-sql-server-express-to-csv-need-quoting-and-escaping If that's not what you need, please rephrase your question. – Robert Calhoun Jan 05 '15 at 18:08

1 Answers1

0

Try this code

DECLARE @table1 TABLE
  (
     id   INT,
     col1 VARCHAR(20)
  )
DECLARE @results TABLE
  (
     id        INT,
     commalist VARCHAR(max)
  )

INSERT INTO @table1
VALUES      (1,
             'Inspection charge'),
            (1,
             'Liftgate @ pickup'),
            (2,
             'Inspection charge'),
            (2,
             'Inside delivery'),
            (2,
             'Inside pickup')

DECLARE @listStr VARCHAR(MAX),
        @id      INT
DECLARE cur CURSOR static FOR
  SELECT DISTINCT id
  FROM   @table1

OPEN cur

FETCH cur INTO @id

WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SELECT @listStr = COALESCE (COALESCE(@listStr+',', '') + col1, @listStr)
      FROM   @table1
      WHERE  id = @id

      INSERT INTO @results
      SELECT @id,
             @listStr

      FETCH cur INTO @id
  END

SELECT *
FROM   @results

CLOSE cur

DEALLOCATE cur
StackUser
  • 5,370
  • 2
  • 24
  • 44