2

I have data like this I just want convert the rows into column

below Data

DECLARE  @Table1 TABLE   
    ( id  int,  name  varchar(20),  val  varchar(20))
;

INSERT INTO @Table1
    ( id ,  name ,  val )
VALUES
    (222, 'ram', 'match'),
    (222, 'ra', 'nomatch'),
    (222, 'man', 'nomatch'),
    (222, 'kim', 'match')
;

I need output like this 

ID   Match      NoMatch
222  ram,kim   ra,man

Actually I have tried Pivot and Max Condition some where I'm missing Point .

Cœur
  • 37,241
  • 25
  • 195
  • 267
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • 1
    this two links should help you http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function http://stackoverflow.com/questions/14618316/how-to-create-a-pivot-query-in-sql-server-without-aggregate-function – JonWay May 26 '16 at 12:46
  • @tom why it has been duplicated what ever the question is not even matching a mile with it – mohan111 May 26 '16 at 13:34

1 Answers1

2

Try this:

SELECT id,
       STUFF((SELECT ',' + name
              FROM @Table1 t2
              WHERE t2.id = t1.id AND val = 'match'
              FOR XML PATH('')), 1, 1, '') AS Match,
       STUFF((SELECT ',' + name
              FROM @Table1 t2
              WHERE t2.id = t1.id AND val = 'nomatch'
              FOR XML PATH('')), 1, 1, '') AS Nomatch

FROM @Table1 t1
GROUP By id
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • @betsos I know this on..can you please tell me in pivot I don't want it as static like match or no match – mohan111 May 26 '16 at 12:36
  • 3
    Pivot is static itself. Pivot or not, to have variable number of columns you need dynamic Sql anyway. – Serg May 26 '16 at 12:41