What up guys,

I'm new here, I've been scavenging the internet looking for an answer to my challenge and to be honest its hard to articulate what I need. I figured this would be my next step.

I have the following table which i need to self join in order to get I what I need. My challenge is that I have to group my label to my value bases on valueA

Ultimately the user wants to see the report formatted so that each value in value a is match to its corresponding Label1/value1 value. They want to see ValueA display label1 as the header and value1 as the value. The thing is, Label1 has different labels based on valueA and consequently different values in Value1. I've been working with pivots and CTEs to try and get what I need but I've been unsuccessful. I believe what I need to do is create a generic label1/value1 column and in ssrs find a way to map the label1 value to the column name based on valueA. I apologized if I'm being confusing, this the first time I have to work with a table like this so it hard to articulate what I'm looking for

create table t( ValueA varchar(50), Valueb varchar(50), label1 VARCHAR (50), label2 VARCHAR (50), value1 VARCHAR(50), Value2 varchar (50));

insert into t values ('abc', 'a1b2c3', 'date', 'name', '1/1/2016', 'Herp');
insert into t values ('def', 'd1e2f3', 'name', 'accnt', 'derp', '12345');

select valueA, Label1, label2, value1,value2
from test t
join test t2 on t.valuea = t2.valueb
pivot (
max(value1) as lbl1
for label1 in(date, name) )
pivot
(max(value2) as lbl2
from label2 in (name, accnt))...


PS: I submitted this question in bad form and I didn't quite understand what they wanted me to change when they sent me the email to update my question so I'm resubmitted since I could not longer update my original question.

VALUEA  VALUEB  DATE      NAME  ACCNT  
def     d1e2f3            derp  12345  
abc     a1b2c3  1/1/2016  Herp

create table t( 
  ValueA varchar(50), Valueb varchar(50), 
  label1 VARCHAR (50), label2 VARCHAR (50), 
  value1 VARCHAR(50), Value2 varchar (50)
);

insert into t values ('abc', 'a1b2c3', 'date', 'name', '1/1/2016', 'Herp');
insert into t values ('def', 'd1e2f3', 'name', 'accnt', 'derp', '12345');

select * from t
unpivot (
  (lab, val) for c in (
    (label1, value1), (label2, value2)
  )
);

VALUEA  VALUEB  C              LAB    VAL       
abc     a1b2c3  LABEL1_VALUE1  date   1/1/2016  
abc     a1b2c3  LABEL2_VALUE2  name   Herp      
def     d1e2f3  LABEL1_VALUE1  name   derp      
def     d1e2f3  LABEL2_VALUE2  accnt  12345 

select * from (
  select valuea, valueb, val, lab from t
  unpivot (
    (lab, val) for c in (
      (label1, value1), (label2, value2)
    )
  )
)
pivot (
  max(val) for lab in (
    'date' as "DATE", 'name' as "NAME", 'accnt' as "ACCNT"
  )
);

VALUEA  VALUEB  DATE      NAME  ACCNT  
def     d1e2f3            derp  12345  
abc     a1b2c3  1/1/2016  Herp