CODEWITHSUNDEEP
sql-serverpivot
coolj
coolj

Reputation: 1

Pivot Operation in SQL Query

I am working on a table which has data like this-- Sorry for formatting as I am new here

ColA  Type    Name    Email 
-----------------------   
1     Type1  John     yyyy@m  
1     Type2  Emily    xxx@m  
2     Type2  Richard  ooo@m  
3     Type1  Rebecca  pppp@m  
3     Type2  Pumpkin   nnnn@m

I want to have an output based on Type Column 

COL1 TYPE1Name TYPE1Email Type2Name Type2Email 
-------------------------   
1    John      yyyy@m      Emily    xxx@m   
2    NULL      NULL        Richard  ooo@m  
3    Rebecca   pppp@m      Pumpkin    nnnn@m

I tried 2 CTE's with the pivots and joining them but did not get the results Any suggestions

Upvotes: 0

Views: 53

Answers (2)

JamieD77
JamieD77

Reputation: 13949

Easiest and best performance would be to use MAX with CASE expressions. This can also be created dynamically if more Types are available.

SELECT
    Col1        = ColA,
    TYPE1Name   = MAX(CASE WHEN [Type] = 'Type1' THEN [Name] END),
    TYPE1Email  = MAX(CASE WHEN [Type] = 'Type1' THEN [Email] END),
    TYPE2Name   = MAX(CASE WHEN [Type] = 'Type2' THEN [Name] END),
    TYPE2Email  = MAX(CASE WHEN [Type] = 'Type2' THEN [Email] END)
FROM
    myTable
GROUP BY
    ColA

Upvotes: 1

prenesh u
prenesh u

Reputation: 122

Joining the pivot worked for me, and I got the result as you required,

SELECT NAME.COL1
,NAME.TYPE1Name
,email.TYPE1Email
,NAME.TYPE2Name
,email.TYPE2Email
FROM (
    SELECT ColA AS COL1
        ,Type1 AS TYPE1Name
        ,Type2 AS TYPE2Name
    FROM (
        SELECT ColA
            ,NAME
            ,Type
        FROM #table_name --Your table name here
        ) AS a
    pivot(max(NAME) FOR Type IN (
                [Type1]
                ,[Type2]
                )) pvt
    ) NAME
INNER JOIN (
    SELECT ColA AS COL1
        ,Type1 AS TYPE1Email
        ,Type2 AS TYPE2Email
    FROM (
        SELECT ColA
            ,Email
            ,Type
        FROM #table_name --Your table name here
        ) AS a
    pivot(max(Email) FOR Type IN (
                [Type1]
                ,[Type2]
                )) pvt
    ) email ON NAME.COL1 = email.COL1

The result is as below,

COL1    TYPE1Name   TYPE1Email  TYPE2Name   TYPE2Email
1       John        yyyy@m      Emily       xxx@m
2       NULL        NULL        Richard     ooo@m
3       Rebecca     pppp@m      Pumpkin     nnnn@m

Upvotes: 1

Related Questions