Cus table:
+----+-------------------------+------+
| ID | Name | Age |
+----+-------------------------+------+
| 1 | Japhet | 22 |
| 2 | Abegail | 31 |
| 3 | Norlee | 35 |
| 4 | Pacita | 60 |
| 5 | Reynaldo | 65 |
| 6 | Barro, Reynaldo Batucan | 65 |
| 7 | Batucan, Japhet C. | NULL |
| 8 | Barro, Reynaldo B. | NULL |
+----+-------------------------+------+
Cus2 table: (Cus and Cus2 has one to many relationship)
+-----+------+-----------------+---------------+
| QID | ID | Name | Country |
+-----+------+-----------------+---------------+
| 1 | 1 | Japhet | |
| | PH | | |
| 2 | 1 | NULL | CN |
| 3 | 1 | Japhet | PH |
| 4 | 1 | Japhet | PH |
| 5 | 2 | NULL | PH |
| 6 | 2 | NULL | CN |
| 7 | 2 | John Hammond | United States |
| 8 | 3 | Mudassar Khan | India |
| 9 | 3 | Suzanne Mathews | France |
| 11 | 4 | Japhe | PH |
| 12 | 4 | Abegail | |
| | PH | | |
| 13 | 4 | Abegail | |
| | US | | |
| 14 | 3 | LOL | |
| | UK | | |
| 15 | 4 | Japhet | |
| | PH | | |
| 16 | 3 | Abegail | |
| | | | |
| 17 | 2 | Japhet | |
| | FR | | |
| 18 | 1 | Japhet | PH |
| 19 | 4 | Japhet | PH |
| 20 | 3 | | NULL |
| 21 | 2 | Abegail | CN |
| 22 | 1 | Japhet | PH |
| 23 | 5 | Japhet | USA |
| 24 | 5 | Abegail | CN |
| 25 | 5 | Japhet | PH |
| 26 | NULL | NULL | NULL |
| 27 | NULL | NULL | NULL |
| 28 | NULL | NULL | NULL |
| 29 | 8 | Japhet | PH |
| 30 | 7 | Abegail | CN |
| 31 | 8 | Japhet | PH |
| 32 | 7 | Abegail | USA |
| 33 | 7 | Abegail | PH |
| 34 | 8 | Abegail | CN |
+-----+------+-----------------+---------------+
I created a pivot and a join
CREATE PROCEDURE [dbo].[Procedure5]
as
SELECT *
FROM Cus S
INNER JOIN (
SELECT *
FROM
(SELECT * FROM Cus2) I
PIVOT (Max(I.Name) FOR I.Country IN (PH, CN, USA)) P
) I ON S.id = I.id
;
RETURN 0
But the output of it has 3 similar names how can I to do it that it will only show all the data in the same rows.
My desired result:
+----------+-----+--------+---------+--------+
| Name | Age | PH | CN | USA |
+----------+-----+--------+---------+--------+
| Reynaldo | 65 | Japhet | Abegail | Japhet |
+----------+-----+--------+---------+--------+
My current output:
+----------+-----+--------+---------+--------+
| Name | Age | PH | CN | USA |
+----------+-----+--------+---------+--------+
| Reynaldo | 65 | | | Japhet |
| Reynaldo | 65 | | Abegail | |
| Reynaldo | 65 | Japhet | | |
+----------+-----+--------+---------+--------+
Your Pivot query should probably look like this.
SELECT *
FROM (SELECT c.Name,
c.Age,
c2.Name [Name2],
c2.Country
FROM Cus c
INNER JOIN Cus2 c2 ON c.Id = c2.Id) t
PIVOT (
MAX(Name2)
FOR Country IN ([PH], [CN], [USA])
) p
When you write a pivot query, make sure you create a derived table, and only select the fields that you want to see in you final result, and that all columns have a distinct name
An alternative to PIVOT would be MAX(Case) expressions. This may perform better than PIVOT.
SELECT c.Name,
c.Age,
MAX(CASE WHEN c2.Country = 'PH' THEN c2.Name END) AS [PH],
MAX(CASE WHEN c2.Country = 'CN' THEN c2.Name END) AS [CN],
MAX(CASE WHEN c2.Country = 'USA' THEN c2.Name END) AS [USA]
FROM Cus c
INNER JOIN Cus2 c2 ON c.Id = c2.Id
GROUP BY c.Name,
c.Age
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments