0

Here is an example table for my problem:

100     IDO     0514443421
100     IDO     0504899721
100     IDO     0508843421
101     LIRAN   0523399721
101     LIRAN   0524899721
102     ERAN    0593369721
102     ERAN    0599999721

I want to get back a row like that:

100, IDO, 0514443421, 0504899721, 0508843421

I'm aware of the problematic fact of the changing rows number. I can settle for fixed 3 output columns, some of them can be empty.

Any good method for achieving this?

Thank u in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ido Gal
  • 528
  • 10
  • 26
  • possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) – dani herrera Feb 26 '12 at 13:43

2 Answers2

4

Assuming a recent SQL Server and based on the the excellent suggestion here, this will select the first two columns separately, and the last part as a comma separated string;

SELECT id, ColA, 
  REPLACE(
    (SELECT ColB AS [data()] 
     FROM TableA a2 
     WHERE a1.id=a2.id 
     ORDER BY a2.ColB FOR XML PATH('')),
     ' ', ', ')
FROM TableA a1
GROUP BY id, ColA;

Demo here.

Edit: Of course you can get it all as a single string if you want, just replace the first line with

SELECT CAST(id AS VARCHAR(16)) + ', ' + ColA + ', ' +
Community
  • 1
  • 1
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Thanks Joachim. Right now I prefer to go with the table based solution as I need to insert the information into a table. – Ido Gal Feb 26 '12 at 20:44
0

Try:

;with cte as 
(select id,
        colA,
        colB,
        rownumber() over (partition by id, colA order by colB) rn
 from myTable)
select id, colA, t1.colB colB1, t2.colB colB2, t3.colB colB3
from cte t1
left join cte t2 on t1.id = t2.id and t1.colA = t2.colA and t2.rn = 2
left join cte t3 on t1.id = t3.id and t1.colA = t3.colA and t3.rn = 3
where t1.rn = 1