Click here to Skip to main content
14,393,112 members
Rate this:
Please Sign up or sign in to vote.
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

 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


I am trying with Pivot concept but I think pivot wont work this case.So I reached out to you.
Posted
Updated 1-May-14 12:47pm
v2
Comments
Andrius Leonavicius 1-May-14 15:30pm
   
Hi,

Are the columns from the data (cid_10, cid_11 and cid_12) fixed or dynamic?
   
Help with what? This is not a question.
—SA
Rate this:
Please Sign up or sign in to vote.

Solution 2

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);
   
v2
Comments
Maciej Los 2-May-14 1:44am
   
Dynamic version of pivot deserves for 5!
Andrius Leonavicius 2-May-14 5:27am
   
Thank you, Maciej.
pratap420 2-May-14 11:09am
   
Excellent thought.Its working as per my requirement .I learnt nice logic today.
Thanks Andrius,Maciej
Maciej Los 2-May-14 11:24am
   
You're very welcome ;)
Andrius Leonavicius 2-May-14 11:25am
   
You're welcome. I'm glad to hear it.
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
v2
Comments
Andrius Leonavicius 1-May-14 19:57pm
   
Hi,

I noticed that your INNER JOIN is missing joining columns...
Maciej Los 2-May-14 1:43am
   
Corrected!
Thank you ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100