Maciej is right. PIVOT does the job.
Here's the dynamic SQL version:
DECLARE @ColumnsTable TABLE (col VARCHAR(10));
INSERT INTO @ColumnsTable (col)
SELECT DISTINCT cid
FROM src_table;
DECLARE @Columns VARCHAR(MAX), @SQL VARCHAR(MAX);
SET @Columns = (SELECT STUFF((SELECT DISTINCT ', [cid_' + CONVERT(VARCHAR(10), col) + ']'
FROM @ColumnsTable
FOR XML PATH('')), 1, 2, ''));
SET @SQL = '(SELECT Trm_id, agt_id, nk_id, ' + @Columns + '
FROM (SELECT st.Trm_id, st.agt_id, st.nk_id, rt.value_desc, ''cid_'' + CONVERT(VARCHAR(10), rt.cid) AS cid_
FROM src_table AS st
INNER JOIN reference_table AS rt
ON ((st.cid = rt.cid) AND (st.valueid = rt.value_id)) ) AS t
PIVOT (MAX(value_desc)
FOR cid_ IN (' + @Columns + ')) AS p);';
EXEC(@SQL);