col data as column header with conditions

Question

2.20/5 (2 votes)

See more:

SQL-Server-2008R2

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 30-Apr-14 20:39pm

pratap420

Updated 1-May-14 11:47am

Maciej Los

v2

Add a Solution

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?

Sergey Alexandrovich Kryukov 1-May-14 17:49pm

Help with what? This is not a question.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Andrius Leonavicius · Accepted Answer · 2014-05-01T13:50:00

Maciej is right. PIVOT does the job.

Here's the dynamic SQL version:

SQL

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

Maciej Los · Accepted Answer · 2014-05-01T11:55:00

Solution 1

Pivot should works perfect! Static version:

SQL

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

Posted 1-May-14 11:55am

Maciej Los

Updated 1-May-14 19:43pm

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