SQL query to pivot a column using MERGE or another faster ways without pivot table

Question

I have the following table:

paymentgateway           status
--------------           --------
1                         1
2                         2
3                         1
2                         3
2                         3
.                         .
.                         .
.                         .

I am trying to write an SQL query to give the following result:

paymentgetways              status1         status2        status3
 --------------              ------         -------        --------
      1                        1               0              1
      2                        0               1              2
      3                        1               0              0
      .
      .
      .

How can I modify my query to give the correct presentation? i dont want to use case when ,Thanks

possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Jul 07 '15 at 17:30

score 0 · Accepted Answer · answered Jul 07 '15 at 07:08

Here is the static pivot query, change this to dynamic based on your needs. Ref Dynamic PIVOT in Sql Server

DECLARE @Test AS TABLE(paymentgateway int, status int)

insert into @Test VALUES(1,1)
insert into @Test VALUES(2,2)
insert into @Test VALUES(3,1)
insert into @Test VALUES(2,3)
insert into @Test VALUES(2,3)

SELECT paymentgateway, [1] AS status1, [2] AS status2, [3] AS status3 FROm @Test
PIVOT (COUNT(Status) FOR Status IN ([1], [2], [3])) AS A

tanks but i have to use merge table or another ways to solve it — arash omid, Jul 07 '15 at 07:15

SQL query to pivot a column using MERGE or another faster ways without pivot table

1 Answers1