Reputation: 195
Add two more columns to c (Table1). Primary key is IMSI. if IMSI has 2 or more APNs, populate it to the additional columns (apn2, apn3).
Query Result 1 :
+------+--------+------------------+-----+
| IMSI | MSISDN | ROUTING_CATEGORY | apn |
+------+--------+------------------+-----+
| AAAA | BBBBB | CCCCCC | NET |
| AAAA | BBBBB | CCCCCC | MMS |
| AAAA | BBBBB | CCCCCC | SSS |
| LLLL | PPPPP | FFFFFF | NET |
+------+--------+------------------+-----+
Expected output:
╔══════╦════════╦══════════════════╦═════╦══════╦══════╗
║ IMSI ║ MSISDN ║ ROUTING_CATEGORY ║ apn ║ apn2 ║ apn3 ║
╠══════╬════════╬══════════════════╬═════╬══════╬══════╣
║ AAAA ║ BBBBB ║ CCCCCC ║ NET ║ MMS ║ SSS ║
║ LLLL ║ PPPPP ║ FFFFFF ║ NET ║ ║ ║
╚══════╩════════╩══════════════════╩═════╩══════╩══════╝
My Query:
SELECT *
FROM
(
SELECT
a.IMSI,
a.MSISDN,
b.ROUTING_CATEGORY,
c.apn
FROM
[Usage].[dbo].[MSISDN_IMSI] a,
[Usage].[dbo].[IMSI_RC] b,
[Usage].[dbo].[IMSI_QOS] c
WHERE
a.IMSI = b.IMSI
AND
c.IMSI = a.IMSI
GROUP BY
a.IMSI,
a.MSISDN,
b.ROUTING_CATEGORY,
c.apn
) AS c
PIVOT
(
) AS [pivot]
Upvotes: 0
Views: 79
Reputation: 355
You can do something like this:
Select IMSI, MSISDN, ROUTING_CATEGORY,
[1] as apn1, [2] as apn2, [3] as apn3
from
(Select IMSI, MSISDN, ROUTING_CATEGORY, apn,
ROW_NUMBER() over(partition by imsi order by apn) as r
from @t
)as c
PIVOT
(
max(apn)
for r in ([1], [2], [3])
)AS p
Upvotes: 1
Reputation: 441
What is the purpose of this query? reporting?
one option is to create a temporary table to insert the distinct values by excluding the apn field then use a loop (while/cursor) to alter the temp table to add the columns dynamically and update the same based on the number of apn field for each unique key.
With Pivot you need to manually specify the columns or else you may need to use dynamic sql with pivot to generate the column.
Upvotes: 0