Nandy
Nandy

Reputation: 57

Conditions in aggregations within Pivot

Table #Temp:

ID    Name        Hobbies
1     xxx          Reading
1     xxx          Sports
1     xxx          Dance
2     xyz          Dance
3     yyy          Swimming
3     yyy          Reading

Now, I want the result set to be:

ID   Name    Reading  Sports  Dance  Swimming  
1    xxx      T        T      T        F
2    xyz      F        F      T        F
3    yyy      T        F      F        T

I have written the following query:

SELECT * FROM
(
    SELECT * FROM #Temp
)SRC
PIVOT
(
    COUNT([ID]) 
    FOR [Hobbies] in ([Reading],[Sports],[Dance],[Swimming])
) piv;

I got the following result:

ID   Name    Reading  Sports  Dance  Swimming  
1    xxx      1        1      1        0
2    xyz      0        0      1        0
3    yyy      1        0      0        1

So, I want to use conditions in aggregations like if (count(id) = 1 then 'T' else 'F'). Is it possible in Pivots and how to accomplish it?

Upvotes: 1

Views: 48

Answers (3)

elle0087
elle0087

Reputation: 911

SELECT case when Reading >0 then 'T' else 'F' end Reading ,
       case when Sports >0 then 'T' else 'F' end Sports ,
       case when Dance >0 then 'T' else 'F' end Dance 

FROM
(
    SELECT * FROM #Temp
)SRC
PIVOT
(
    COUNT([ID]) 
    FOR [Hobbies] in ([Reading],[Sports],[Dance],[Swimming])
) piv;

Upvotes: 1

SELECT 
    Name,
    CASE Reading WHEN 1 THEN 'T' ELSE 'F' END AS Reading,
    CASE Sports WHEN 1 THEN 'T' ELSE 'F' END AS Sports,
    CASE Dance WHEN 1 THEN 'T' ELSE 'F' END AS Dance,
    CASE Swimming WHEN 1 THEN 'T' ELSE 'F' END AS Swimming
FROM
(
    SELECT * FROM @tblTest
)SRC
PIVOT
(
    COUNT([ID]) 
    FOR [Hobbies] in ([Reading],[Sports],[Dance],[Swimming])
) piv;

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this

SELECT ID,NAME,
       CASE WHEN Reading=1 THEN 'T' ELSE 'F' END AS Reading,
       CASE WHEN Sports=1 THEN 'T' ELSE 'F' END AS Sports,
       CASE WHEN Dance=1 THEN 'T' ELSE 'F' END AS Dance,
       CASE WHEN Swimming =1 THEN 'T' ELSE 'F' END AS Swimming ,
FROM(
        --your query here
)t 

Upvotes: 2

Related Questions