Reputation: 57
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
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
Reputation: 14669
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
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