CODEWITHSUNDEEP
sqlsql-servert-sqlpivottranspose
starpark
starpark

Reputation: 195

SQL pivot after join query

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

Answers (2)

Mangal Pardeshi
Mangal Pardeshi

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

Sandeep T
Sandeep T

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

Related Questions