0

How do I simply switch Rows with Column in SQL with count? Is there any way to do? Actually i want against each unique mobile no with count and product name side by side means if two mobile no in table then count = 2 and side of that two product1,product2 shows in output.

ie turn this result:

Srno    Name| Mobile| Count  |  ProductName
1       xyz   1234       1       LNM
2       PQR   5678       1       VCD
3       xyz   1234       1       KLM
4       PQR   5678       1       NMG

into this:

Srno    Name| Mobile| Count  |  ProductName1  | ProductName2 
1       xyz   1234       2       LNM              KLM
2       PQR   5678       2       VCD              NMG
lucky one
  • 21
  • 2
  • Possible duplicate of [Simple way to transpose columns and rows in Sql?](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) – OzrenTkalcecKrznaric Jan 09 '19 at 08:56

2 Answers2

1

If you have a limited products then you use row_number() & do conditional aggregation :

select min(srno) as srno, name, Mobile, count(*) as cnt,
       max(case when seq = 1 then ProductName end) as ProductName1,
       max(case when seq = 2 then ProductName end) as ProductName2
from (select t.*,
             row_number() over (partition by name, Mobile order by srno) as seq
      from table t
     ) t
group by name, Mobile;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

Try this:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS Srno,
    T1.name,
    T1.Mobile,
    SUM(T1.xCount + T2.xCount) as cnt,
    max(T1.ProductName) as Product1,
    max(T2.ProductName) as Product2
FROM Table1 as T1
LEFT JOIN Table1 AS T2 ON T1.name = T2.name
                      AND T2.srno > T1.srno
GROUP BY T1.name, T1.Mobile
ORDER BY Srno
Piyush aggarwal
  • 750
  • 2
  • 14
  • 25