Hm... and what about the 'B' in column FEB ? Is that a typo or correct value?

In case it is correct value, you need to specify what it means... for the present, I will suppose that only value '1' means that the current type is "active" for the month, any other value means it is not.

Then it would be possible to rewrite the query this way (it depends on the fact that TYPE will never be 'ZZZ' ... I wouldn't like to suppose that in my database, but also I would try to solve this problem before data acquire such unpleasant format) :

SELECT Q.siteid, 'XX',

CASE WHEN MAX(Q.jan)='ZZZ' THEN 'LC' ELSE MAX(Q.jan) END as jan,

CASE WHEN MAX(Q.feb)='ZZZ' THEN 'LC' ELSE MAX(Q.feb) END as feb,

CASE WHEN MAX(Q.mar)='ZZZ' THEN 'LC' ELSE MAX(Q.mar) END as mar,

CASE WHEN MAX(Q.apr)='ZZZ' THEN 'LC' ELSE MAX(Q.apr) END as apr,

CASE WHEN MAX(Q.may)='ZZZ' THEN 'LC' ELSE MAX(Q.may) END as may,

CASE WHEN MAX(Q.jun)='ZZZ' THEN 'LC' ELSE MAX(Q.jun) END as jun,

CASE WHEN MAX(Q.jul)='ZZZ' THEN 'LC' ELSE MAX(Q.jul) END as jul

FROM (SELECT siteid, type

, CASE WHEN jan='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as jan

, CASE WHEN feb='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as feb

, CASE WHEN mar='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as mar

, CASE WHEN apr='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as apr

, CASE WHEN may='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as may

, CASE WHEN jun='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as jun

, CASE WHEN jul='1' THEN CASE WHEN type='LC' THEN 'ZZZ' ELSE type END END as jul

FROM #data d) as Q

GROUP BY Q.siteid

As I said, this solution is far from perfect, but it does what you were asking for and it should work. You know more about your data, so you may be able to come up with something similar, but better suited for your environment.

Good luck!