Cross apply along with additional columns

Question

I have Two tables 1. #SetValuesTable 2. #labelTempTab

Data in #SetValuesTable look like this :

MNUM      la1   la2     la3   la4    PropertyType 
12         1     0      2             s
13         4     0      5      7      p

Data in #labelTempTab look like this :

RowNum  Label Title PropertyType  
 1      la1  Agent1    s
 2      la2  Agent2    s
 3      la3  Agent3    s
 4      la1  Agent1    p
 5      la2  Agent2    p
 6      la3  Agent3    p
 7      la4  Agent4    p

I need the result table like this :

MNUM LabelName LabelValue   PropertyType 
 12    la1       1               s
 12    la2       0               s
 12    la3       2               s
 13    la1       4               p
 13    la2       0               p
 13    la3       5               p
 13    la4       7               p

Query :

  SELECT MNUM, LabelName , LabelValue  FROM #SetValuesTable 
     CROSS APPLY ( VALUES '
     stuff(( SELECT ',('''+ replace(C.label,'''','"') + ''',' + quotename(C.label) + ')'   FROM #labelTempTab c group by label FOR xml path('')), 1, 1, '')
         ) AS UPTab (Label , LabelValue);

The above query will result in :

MNUM LabelName LabelValue    
 12    la1       1               
 12    la2       0               
 12    la3       2               
 13    la1       4               
 13    la2       0               
 13    la3       5               
 13    la4       7

Can some body help me to get the rest of the columns also.

Note : I tried to unpiovt the tabel using UNPIVOT but the performance is not so good. With cross apply the performance is really good.

Gordon the query i posted is only a portion of the bigger one. A solution for regular SQL will also work for me. modified the question. — BumbleBee, Jan 27 '15 at 19:55
You said you have 500+ columns, but I can only count 10 columns in both of your tables. Where are the rest? — Aducci, Jan 27 '15 at 20:19

score 0 · Answer 1 · answered Jan 27 '15 at 20:06

0

You can do this with a join:

select st.MNUM, lt.LabelName,
       (case when lt.LabelName = 'la1' then la1
             when lt.LabelName = 'la2' then la2
             when lt.LabelName = 'la3' then la3
             when lt.LabelName = 'la4' then la4    
        end) as LabelValue,
       st.PropertyType 
from #labelTempTab lt join
     #SetValuesTable st
     on st.mnum = lt.label

answered Jan 27 '15 at 20:06

Gordon Linoff

1,242,037
58
646
786

Tried doing with join but because of huge amount of data the performance is very slow. Also the table contains minumum 500+ columns if I have to use case then the query will be humongous. – BumbleBee Jan 27 '15 at 20:07
How quick does it have to perform? Any time you manipulate a "huge amount of data" it's going to take some time. For my money, I still think your best bet is an unpivot. – Xedni Jan 27 '15 at 20:11
With UNPIVOT now it is taking about 90 secs. – BumbleBee Jan 27 '15 at 20:12
@BumbleBee What's the performance with CROSS APPLY? – Taryn Jan 27 '15 at 20:38
with Cross apply the unpivot is happening in 12 secs. – BumbleBee Jan 27 '15 at 21:46

Cross apply along with additional columns

1 Answers1