How to do rows to column in SQL Server with count

Question

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

Possible duplicate of Simple way to transpose columns and rows in Sql? — OzrenTkalcecKrznaric
– OzrenTkalcecKrznaric, Commented Jan 9, 2019 at 8:56

Yogesh Sharma · Accepted Answer · 2019-01-11 12:34:25Z

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;

edited Jan 11, 2019 at 12:34

answered Jan 9, 2019 at 8:57

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lucky one Over a year ago

output gives duplicate rows comes in result. i want distinct mobile.

Piyush aggarwal · Accepted Answer · 2019-01-09 10:45:05Z

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

edited Jan 9, 2019 at 10:45

answered Jan 9, 2019 at 9:51

Piyush aggarwal

7722 gold badges15 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to do rows to column in SQL Server with count

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related