Pivot Query Question - Can I have 2 columns in a Pivot?

I want two columns in Pivot format. Is it possible.

I have the following table:
CREATE TABLE CONDITION DETAILS
(
CONDITIONID INT,
NAME VARCHAR(100),
DESCRIPTION VARCHAR(50),
USERNAME VARCHAR(50),
NOTIFY_FREQUENCY VARCHAR(10))

INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','First Person Notified','JC','Daily')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Second Person Notified','BG','Weekly')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Third Person Notified','BG','Monthly')

Now I have the following statement
SELECT * FROM
(SELECT CONDITIONID, NAME, DESCRIPTION, USERNAME
--, NOTIFY_FREQUENCY
FROM CONDITION) SOURCEQUERY
PIVOT (MIN(USERNAME) FOR [DESCRIPTION] IN ([First Person Notified], [Second Person Notified], [Third Person Notified])
) AS PIVOTTABLE

Output:
CONDITIONID NAME First Person Notified Second Person Notified Third Person Notified
1 Receipt of statements JC BG BG

I also want the Frequency to be displayed, I want output to be like
1 Receipt of Statements Daily JC Weekly BG Monthly BG

Can anyone suggest. Thanks.

The first thing that I want to do is to give kudos for such an outstanding job of perparing your question. Having the table definition and the insert statements for the test data made this much easier to set up for testing. Nice Work!

I have to confess that my work on this might not be as good of an effort as the question. I have never done a double-pivot so I really am not sure what is the best way to go about it. This seems to work. I would really like commentary from Umachandar or someone similar who is familar with a "best approach."

SELECT conditionId,
name,
rtrim(substring([First Person Notified], 11, 50)) as [1st Person],
rtrim(left([First Person Notified], 10)) as [1st Frequency],
rtrim(substring([Second Person Notified],11, 50)) as [2nd Person],
rtrim(left([Second Person Notified],10)) as [2nd Frequency],
rtrim(substring([Third Person Notified], 11, 50)) as [3rd Frequency],
rtrim(left([Third Person Notified],10)) as [3rd Person]
FROM ( SELECT CONDITIONID,
NAME,
DESCRIPTION,
cast (isnull(NOTIFY_FREQUENCY, '') as char(10))
+ username
as pivotStuff
FROM CONDITION
) SOURCEQUERY
PIVOT ( MIN(pivotStuff) FOR [DESCRIPTION]
IN ( [First Person Notified],
[Second Person Notified],
[Third Person Notified]
) ) AS PIVOTTABLE

-- conditionId name 1st Person 1st Frequency 2nd Person 2nd Frequency 3rd Frequency 3rd Person
-- -- - -- - -- - -- -
-- 1 Receipt of statements JC Daily BG Weekly BG Monthly

|||

Thanks. This sure works. Had thought about this solution but was not sure if it was right way to do, so had posted my query on to the forums.

Please let me know if there is a better way to achive the above results.