簡單列轉換

Question

create table #userreport_forusers_Master
(
UserID nvarchar(100)
,Num varchar(50)
,ComID nvarchar(100)
,ModelName nvarchar(100)
,CreateDate datetime
)
insert #userreport_forusers_Master
select 'mis_hh','DC00001','002','1;1;0;0;0;0;0;0;0;1;0','Mar 10 2006 12:00AM' union
select 'mis_ql','DC00003','002','0;0;0;0;0;1;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'qn_he','DC00004','002','0;0;0;0;0;0;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'mis_lz','DC00005','003','0;0;0;0;0;0;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'mis_bjarne','DC00006','003','0;0;0;0;0;0;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'GX_LU','DC00009','002','0;0;1;1;0;0;0;0;0;0;0','Mar 11 2006 12:00AM' union
select 'zw_wang','DC00011','002','1;0;1;1;1;0;1;1;0;0;1','Mar 11 2006 12:00AM' union
select 'meimeng','DC00012','003','0;1;0;0;0;0;0;0;0;0;0','Mar 11 2006 12:00AM' union
select 'JH_TANG','DC00013','003','0;1;0;0;0;0;0;0;0;0;0','Mar 11 2006 12:00AM' union
select 'cy_huang','DC00014','002','1;0;1;1;1;0;0;1;0;0;0','Mar 11 2006 12:00AM' create table #UsedModular
(Ma nvarchar(100),Mb nvarchar(100),Mc nvarchar(100),Md nvarchar(100),Me nvarchar(100),
Mf nvarchar(100),Mg nvarchar(100),Mh nvarchar(100),Mi nvarchar(100),Mj nvarchar(100),Mk nvarchar(100))
insert #UsedModular
select '倉存      ','采購      ','銷售系統  ','產品工程  ','程控中心  ','工作中心  ','QC','成品入庫','財務相關','人事考勤','必要條件' select * from #userreport_forusers_Master
select * from #UsedModular
--drop table #userreport_forusers_Master
--drop table #UsedModular
--表#userreport_forusers_Master的-ModelName1,0 對應表#UsedModular的響應第幾個字段
--要求結果為2個字段select A,B--------------------------------------
A                       B
mis_hh 倉存      ;采購      ;人事考勤;
mis_ql 工作中心  ;
qn_he
mis_lz
mis_bjarne
GX_LU 銷售系統  ;產品工程  ;
zw_wang 倉存      ;銷售系統  ;產品工程  ;程控中心  ;QC;成品入庫;必要條件;
meimeng 采購      ;
JH_TANG 采購      ;
cy_huang 倉存      ;銷售系統  ;產品工程  ;程控中心  ;成品入庫;
cp_jiang 銷售系統  ;產品工程  ;程控中心  ;
xu_pu 采購      ;
rf_liang 倉存      ;采購      ;銷售系統  ;產品工程  ;程控中心  ;QC;必要條件;

Answer 1

select
    a.UserID,
    stuff(case substring(ModelName, 1,1) when 1 then ';'+Ma else '' end+
          case substring(ModelName, 3,1) when 1 then ';'+Mb else '' end+
          case substring(ModelName, 5,1) when 1 then ';'+Mc else '' end+
          case substring(ModelName, 7,1) when 1 then ';'+Md else '' end+
          case substring(ModelName, 9,1) when 1 then ';'+Me else '' end+
          case substring(ModelName,11,1) when 1 then ';'+Mf else '' end+
          case substring(ModelName,13,1) when 1 then ';'+Mg else '' end+
          case substring(ModelName,15,1) when 1 then ';'+Mh else '' end+
          case substring(ModelName,17,1) when 1 then ';'+Mi else '' end+
          case substring(ModelName,19,1) when 1 then ';'+Mj else '' end+
          case substring(ModelName,21,1) when 1 then ';'+Mk else '' end,1,1,'')+';'
from #userreport_forusers_Master a,#UsedModular b/*
UserID        ModelName
------------  -----------------------------------------------------------------
cy_huang      倉存      ;銷售系統  ;產品工程  ;程控中心  ;成品入庫
GX_LU         銷售系統  ;產品工程  
JH_TANG       采購      
meimeng       采購      
mis_bjarne    NULL
mis_hh        倉存      ;采購      ;人事考勤
mis_lz        NULL
mis_ql        工作中心  
qn_he         NULL
zw_wang       倉存      ;銷售系統  ;產品工程  ;程控中心  ;QC;成品入庫;必要條件
*/

Answer 2

select
    a.UserID,
    case substring(ModelName, 1,1) when 1 then Ma+';' else '' end+
    case substring(ModelName, 3,1) when 1 then Mb+';' else '' end+
    case substring(ModelName, 5,1) when 1 then Mc+';' else '' end+
    case substring(ModelName, 7,1) when 1 then Md+';' else '' end+
    case substring(ModelName, 9,1) when 1 then Me+';' else '' end+
    case substring(ModelName,11,1) when 1 then Mf+';' else '' end+
    case substring(ModelName,13,1) when 1 then Mg+';' else '' end+
    case substring(ModelName,15,1) when 1 then Mh+';' else '' end+
    case substring(ModelName,17,1) when 1 then Mi+';' else '' end+
    case substring(ModelName,19,1) when 1 then Mj+';' else '' end+
    case substring(ModelName,21,1) when 1 then Mk+';' else '' end as ModelName
from
    #userreport_forusers_Master a,
    #UsedModular b
/*
UserID        ModelName
------------  -----------------------------------------------------------------
cy_huang      倉存      ;銷售系統  ;產品工程  ;程控中心  ;成品入庫;
GX_LU         銷售系統  ;產品工程  ;
JH_TANG       采購      ;
meimeng       采購      ;
mis_bjarne    
mis_hh        倉存      ;采購      ;人事考勤;
mis_lz        
mis_ql        工作中心  ;
qn_he         
zw_wang       倉存      ;銷售系統  ;產品工程  ;程控中心  ;QC;成品入庫;必要條件;
*/

Answer 3

create table #userreport_forusers_Master
(
UserID nvarchar(100)
,Num varchar(50)
,ComID nvarchar(100)
,ModelName nvarchar(100)
,CreateDate varchar(30)
)
insert #userreport_forusers_Master
select 'mis_hh','DC00001','002','1;1;0;0;0;0;0;0;0;1;0','Mar 10 2006 12:00AM' union
select 'mis_ql','DC00003','002','0;0;0;0;0;1;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'qn_he','DC00004','002','0;0;0;0;0;0;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'mis_lz','DC00005','003','0;0;0;0;0;0;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'mis_bjarne','DC00006','003','0;0;0;0;0;0;0;0;0;0;0','Mar 10 2006 12:00AM' union
select 'GX_LU','DC00009','002','0;0;1;1;0;0;0;0;0;0;0','Mar 11 2006 12:00AM' union
select 'zw_wang','DC00011','002','1;0;1;1;1;0;1;1;0;0;1','Mar 11 2006 12:00AM' union
select 'meimeng','DC00012','003','0;1;0;0;0;0;0;0;0;0;0','Mar 11 2006 12:00AM' union
select 'JH_TANG','DC00013','003','0;1;0;0;0;0;0;0;0;0;0','Mar 11 2006 12:00AM' union
select 'cy_huang','DC00014','002','1;0;1;1;1;0;0;1;0;0;0','Mar 11 2006 12:00AM' create table #UsedModular
(Ma nvarchar(100),Mb nvarchar(100),Mc nvarchar(100),Md nvarchar(100),Me nvarchar(100),
Mf nvarchar(100),Mg nvarchar(100),Mh nvarchar(100),Mi nvarchar(100),Mj nvarchar(100),Mk nvarchar(100))
insert #UsedModular
select '倉存      ','采購      ','銷售系統  ','產品工程  ','程控中心  ','工作中心  ','QC','成品入庫','財務相關','人事考勤','必要條件' 
select UserID,
b= stuff(case when substring(ModelName,1,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,1,1) = '1' then (select ma from #UsedModular) end,'')+
   case when substring(ModelName,3,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,3,1) = '1' then (select mb from #UsedModular) end,'')+
   case when substring(ModelName,5,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,5,1) = '1' then (select mc from #UsedModular) end,'')+
   case when substring(ModelName,7,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,7,1) = '1' then (select md from #UsedModular) end,'')+
   case when substring(ModelName,9,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,9,1) = '1' then (select me from #UsedModular) end,'')+
   case when substring(ModelName,11,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,11,1) = '1' then (select mf from #UsedModular) end,'')+
   case when substring(ModelName,13,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,13,1) = '1' then (select mh from #UsedModular) end,'')+
   case when substring(ModelName,15,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,15,1) = '1' then (select mi from #UsedModular) end,'')+
   case when substring(ModelName,17,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,17,1) = '1' then (select mj from #UsedModular) end,'')+
   case when substring(ModelName,19,1) = '1' then ';' else '' end+isnull(case when substring(ModelName,19,1) = '1' then (select mk from #UsedModular) end,''),1,1,'')
from #userreport_forusers_Master
drop table #userreport_forusers_Master
drop table #UsedModular