Loading

SELECT DISTINCT MOBILE AND NAME1,NAME2.................

JAYRAM

13y1.5k

I HAVE TABLE like this

SNO MOBILE NAME
1 9849313820 VAMSHI
2 9849313820 RAJU
3 9849313820 PRANAY
4 9000066621 SUPRAJ
5 9000066621 ARAVIND
6 9866029167 ANR
7 9866029167 SUVARNA

SELECT DISTINCT MOBILE NUMBER AND SELECT NAMES1,NAME2,NAME3......
i wnt select like this....
SNO MOBILE NAME 1 NAME 2 NAME 3
1 9849313820 VAMSHI RAJU PRANAY
4 9000066621 SUPRAJ ARAVIND
6 9866029167 ANR SUVARNA

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Resmi Satish13y ago
Accepted answer
Please try the below query

WITH MobileData AS
(
SELECT Name ,Mobile,
Row_Number()OVER(PARTITION BY Mobile ORDER BY Name) AS MobileDisplay,
SNo
FROM
TableName
)
SELECT
    Mobile,
    MAX((CASE WHEN MobileDisplay = '1' THEN Name ELSE '' END)) AS Name1,
    MAX((CASE WHEN MobileDisplay ='2' THEN Name ELSE '' END)) AS Name2,
    MAX((CASE WHEN MobileDisplay ='3' THEN Name ELSE '' END)) AS Name3

FROM
    MobileData
GROUP BY
    Mobile
JAYRAM13y ago
v v thanks sathish ...
Riddhi Valecha13y ago
Hi...
try the following query-

select distince mobilenumber, distinct name1, distinct name2 from table;