col数据作为带有条件的列标题 [英] col data as column header with conditions

问题描述

大家好，

谁能帮我满足以下要求

我有两个这样的桌子

src_table

Hi all,

Could any one help me for below requirement

I have two table like this

src_table

 Trm_id   agt_id    nk_id  cid  valueid
 t1       a1        n1      10     101
 t2       a2        n2      10     101
 t3       a3        n3      10     102
 t1       a1        n1      11     104
 t2       a2        n2      11     105
 t3       a3        n3      11     105
 t1       a1        n1      12     106
 t2       a2        n2      12     106
 t3       a3        n3      12     107

reference_table

reference_table

 ref_id   value_id   value_desc   cid
   1       101        x12         10
   2       102        x13         10
   3       103        x23         10
   4       104        y12         11  
   5       105        y13         11    
   6       106        z01         12   
   7       107        z02         12

现在，我将显示结果集，如

Now I would show my result set like

Trm_id   agt_id    nk_id    cid_10   cid_11    cid_12
t1        a1        n1       X12     Y12     z01
t2        a2        n2       X12     y13     z01
t3        a3        n3       x13     y13     z02

我正在尝试使用Pivot概念，但我认为在这种情况下，pivot无法正常工作.因此，我联系了您.

I am trying with Pivot concept but I think pivot wont work this case.So I reached out to you.

推荐答案

Maciej 是正确的. PIVOT可以胜任.

这是动态SQL版本:

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);

数据透视应该完美！静态版本:

Pivot should works perfect! Static version:

SELECT Trm_id, agt_id, nk_id, cid_10, cid_11, cid_12
FROM (
    SELECT t1.Trm_id, t1.agt_id, t1.nk_id, t1.cid, 'cid_' + CONVERT(VARCHAR(10),t1.cid) AS ColHeader, t1.valueid, t2.value_desc
    FROM src_table AS t1 INNER JOIN reference_table AS t2 ON t1.value_id = t2.value_id
    ) AS DT
PIVOT(MAX(value_desc) FOR ColHeader IN([cid_10],[cid_11],[cid_12])) AS PT

这篇关于col数据作为带有条件的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！