none
sql query to transform data

    Question

  • Dear All,

    I have a table with 8 columns and column name like col1,col2,col3,col4,col5,col6,col7,col8

    col1 data starts with 'CN=********'

    col2 data can be start with 'CN=********' or 'OU=******' or 'DC=*********'

    col3 data can be start with 'OU=******' or 'DC=*********'

    My test case is as below

    create table dtest
    
    (
    
    col1 varchar(30),
    
    col2 varchar(30),
    
    col3 varchar(30),
    
    col4 varchar(30),
    
    col5 varchar(30),
    
    col6 varchar(30),
    
    col7 varchar(30),
    
    col8 varchar(30));
    
    
    
    insert into dtest values ('CN=ABC','CN=XYZ','OU=A','OU=B','OU=C','DC=D1','DC=D2','DC=D3');
    
    insert into dtest (col1,col2,col3) values ('CN=ABC','OU=A','DC=D2');
    
    insert into dtest (col1,col2,col3,col4) values ('CN=ABC','CN=XYZ','OU=B','DC=D1');
    
    insert into dtest (col1,col2,col3,col4) values ('CN=ABC','CN=XYZ','OU=B','OU=C');
    
    insert into dtest (col1,col2,col3,col4,col5,col6) values ('CN=ABC','CN=XYZ','OU=B','OU=C','DC=D1','DC=D2');
    
    
    
    select * from dtest;
    
    
    
    COL1   COL2  COL3  COL4  COL5  COL6  COL7  COL8
    
    ---------- ---------- ---------- -------- -------- -------- --------  --------
    
    CN=ABC  CN=XYZ  OU=A  OU=B  OU=C  DC=D1 DC=D2 DC=D3
    
    CN=ABC  OU=A   DC=D2
    
    CN=ABC  CN=XYZ  OU=B  DC=D1
    
    CN=ABC  CN=XYZ  OU=B  OU=C
    
    CN=ABC  CN=XYZ  OU=B  OU=C  DC=D1 DC=D2
    
    
    
    Expected output is as below.
    
    
    
    CN1	CN2	OU1	OU2	OU3	DC1	DC2	DC3
    
    ABC	XYZ	A	B	C	D1	D2	D3
    
    ABC	N.A	A	N.A	N.A	D2	N.A	N.A
    
    ABC	XYZ	B	N.A	N.A	D1	N.A	N.A
    
    ABC	XYZ	B	C	N.A	N.A	N.A	N.A
    
    ABC	XYZ	B	C	N.A	D1	N.A	N.A
    
    

    With Regards, Khokhar Murtuja S Database Administrator
    Tuesday, April 12, 2011 8:08 AM

Answers

  • This will give you the result: 
    (BTW, the last line on your expected result should be ABC XYZ B C N.A D1 D2 N.A)

    with T1 as (
       select RN = row_number() over (order by col1), * from dtest
    ), T2 as (
    select RN, ColName, Val
     from (select RN, col1, col2, col3, col4, col5, col6, col7, col8 from T1) a
     unpivot (Val for ColName in (col1, col2, col3, col4, col5, col6, col7, col8)) as x
    ), T3 as ( 
    select RN, C1 = left(Val, 2) + str(row_number() over (partition by RN, left(Val, 2) order by ColName), 1),
       C2 = substring(Val, 4, 30) from T2
    )
    select isnull(CN1, 'N.A') CN1, isnull(CN2, 'N.A') CN2, isnull(OU1, 'N.A') OU1, isnull(OU2, 'N.A') OU2,
       isnull(OU3, 'N.A') OU3, isnull(DC1, 'N.A') DC1, isnull(DC2, 'N.A') DC2, isnull(DC3, 'N.A') DC3
    from 
    (select RN, c1, c2 from t3) as SourceTable
    pivot (
       min (c2) for c1 in ([CN1], [CN2], [OU1], [OU2], [OU3], [DC1], [DC2], [DC3])
    ) as PivotTable

    • Proposed as answer by Sali SQL Tuesday, April 12, 2011 10:05 AM
    • Marked as answer by Murtuja Wednesday, April 13, 2011 3:40 AM
    Tuesday, April 12, 2011 10:04 AM
  • I won't bother to try using case statement for this particular case, quickly you will find it is nearly unmanageable or hard to maintain. The most easiest and intuitive way is to update your table to the layout you want like this:

    declare @t table(COL1 varchar(10),COL2 varchar(10),COL3 varchar(10),COL4 varchar(10),COL5 varchar(10),COL6 varchar(10),COL7 varchar(10),COL8 varchar(10))
    
    insert into @t values ('CN=ABC','CN=XYZ','OU=A','OU=B','OU=C','DC=D1','DC=D2','DC=D3');
    
    insert into @t (col1,col2,col3) values ('CN=ABC','OU=A','DC=D2');
    
    insert into @t (col1,col2,col3,col4) values ('CN=ABC','CN=XYZ','OU=B','DC=D1');
    
    insert into @t (col1,col2,col3,col4) values ('CN=ABC','CN=XYZ','OU=B','OU=C');
    
    insert into @t (col1,col2,col3,col4,col5,col6) values ('CN=ABC','CN=XYZ','OU=B','OU=C','DC=D1','DC=D2');
    
    update @t
    Set COL2= NULL,
    	COL3=COL2,
    	COL4=COL3,
    	COL5=COL4,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col2,2) != 'CN'
    
    update @t
    Set COL3 = NULL,
    	COL4=COL3,
    	COL5=COL4,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col3,2) != 'OU'
    
    update @t
    Set COL4= NULL,
    	COL5=COL4,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col4,2) != 'OU'
    
    update @t
    Set COL5=NULL,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col5,2) != 'OU'
    
    update @t
    Set COL6=NULL,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col6,2) != 'DC'
    
    update @t
    Set COL7=NULL,
    	COL8=COL7
    Where 	LEFT(Col7,2) != 'DC'
    
    
    Select 
    		REPLACE(ISNULL(COL1,'N.A'),'CN=','') As CN1
    		, REPLACE(ISNULL(COL2,'N.A'),'CN=','') As CN2
    		, REPLACE(ISNULL(COL3,'N.A'),'OU=','') As OU1
    		, REPLACE(ISNULL(COL4,'N.A'),'OU=','') As OU2
    		, REPLACE(ISNULL(COL5,'N.A'),'OU=','') As OU3
    		, REPLACE(ISNULL(COL6,'N.A'),'DC=','') As DC1
    		, REPLACE(ISNULL(COL7,'N.A'),'DC=','') As DC2
    		, REPLACE(ISNULL(COL8,'N.A'),'DC=','') As DC3
    From	@t
    • Marked as answer by Murtuja Wednesday, April 13, 2011 4:16 AM
    Tuesday, April 12, 2011 11:22 AM

All replies

  • Try to use

    Case statement, Right and Len Functions

     Example

    select

     

    CASE WHEN LEN(COL1) = 6 THEN Right(col1,3) END,

     

    CASE WHEN LEN(Col2) = 6 then Right (col2,3) ELSE Right(col2,1) END,

     

    CASE WHEN LEN(Col3) = 5 THEN Right(Col3,2) ELSE Right(Col3,1) END

     

    .....

    from

    dtest


    /R.
    Tuesday, April 12, 2011 8:18 AM
  • Hi,

    Here is a partial example to do

    declare @t table(COL1 varchar(10),COL2 varchar(10),COL3 varchar(10),COL4 varchar(10),COL5 varchar(10),COL6 varchar(10),COL7 varchar(10),COL8 varchar(10))

    insert into @t
    select 'CN=ABC',  'CN=XYZ',  'OU=A'  ,'OU=B',  'OU=C' , 'DC=D1', 'DC=D2' ,'DC=D3'
    union all
    select 'CN=ABC' , 'OU=A'  , 'DC=D2',null,null,null,null,null
    union all
    select 'CN=ABC',  'CN=XYZ' , 'OU=B' , 'DC=D1',null,null,null,null
    union all
    select 'CN=ABC',  'CN=XYZ' , 'OU=B',  'OU=C',null,null,null,null
    union all
    select 'CN=ABC',  'CN=XYZ'  ,'OU=B'  ,'OU=C' , 'DC=D1' ,'DC=D2',null,null

    select SUBSTRING(COL1,PATINDEX('%=%',col1)+1,LEN(col1)) as CN1  from @t

     


    Thanks and regards, Rishabh
    Tuesday, April 12, 2011 8:24 AM
  • This will give you the result: 
    (BTW, the last line on your expected result should be ABC XYZ B C N.A D1 D2 N.A)

    with T1 as (
       select RN = row_number() over (order by col1), * from dtest
    ), T2 as (
    select RN, ColName, Val
     from (select RN, col1, col2, col3, col4, col5, col6, col7, col8 from T1) a
     unpivot (Val for ColName in (col1, col2, col3, col4, col5, col6, col7, col8)) as x
    ), T3 as ( 
    select RN, C1 = left(Val, 2) + str(row_number() over (partition by RN, left(Val, 2) order by ColName), 1),
       C2 = substring(Val, 4, 30) from T2
    )
    select isnull(CN1, 'N.A') CN1, isnull(CN2, 'N.A') CN2, isnull(OU1, 'N.A') OU1, isnull(OU2, 'N.A') OU2,
       isnull(OU3, 'N.A') OU3, isnull(DC1, 'N.A') DC1, isnull(DC2, 'N.A') DC2, isnull(DC3, 'N.A') DC3
    from 
    (select RN, c1, c2 from t3) as SourceTable
    pivot (
       min (c2) for c1 in ([CN1], [CN2], [OU1], [OU2], [OU3], [DC1], [DC2], [DC3])
    ) as PivotTable

    • Proposed as answer by Sali SQL Tuesday, April 12, 2011 10:05 AM
    • Marked as answer by Murtuja Wednesday, April 13, 2011 3:40 AM
    Tuesday, April 12, 2011 10:04 AM
  • I won't bother to try using case statement for this particular case, quickly you will find it is nearly unmanageable or hard to maintain. The most easiest and intuitive way is to update your table to the layout you want like this:

    declare @t table(COL1 varchar(10),COL2 varchar(10),COL3 varchar(10),COL4 varchar(10),COL5 varchar(10),COL6 varchar(10),COL7 varchar(10),COL8 varchar(10))
    
    insert into @t values ('CN=ABC','CN=XYZ','OU=A','OU=B','OU=C','DC=D1','DC=D2','DC=D3');
    
    insert into @t (col1,col2,col3) values ('CN=ABC','OU=A','DC=D2');
    
    insert into @t (col1,col2,col3,col4) values ('CN=ABC','CN=XYZ','OU=B','DC=D1');
    
    insert into @t (col1,col2,col3,col4) values ('CN=ABC','CN=XYZ','OU=B','OU=C');
    
    insert into @t (col1,col2,col3,col4,col5,col6) values ('CN=ABC','CN=XYZ','OU=B','OU=C','DC=D1','DC=D2');
    
    update @t
    Set COL2= NULL,
    	COL3=COL2,
    	COL4=COL3,
    	COL5=COL4,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col2,2) != 'CN'
    
    update @t
    Set COL3 = NULL,
    	COL4=COL3,
    	COL5=COL4,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col3,2) != 'OU'
    
    update @t
    Set COL4= NULL,
    	COL5=COL4,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col4,2) != 'OU'
    
    update @t
    Set COL5=NULL,
    	COL6=COL5,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col5,2) != 'OU'
    
    update @t
    Set COL6=NULL,
    	COL7=COL6,
    	COL8=COL7
    Where 	LEFT(Col6,2) != 'DC'
    
    update @t
    Set COL7=NULL,
    	COL8=COL7
    Where 	LEFT(Col7,2) != 'DC'
    
    
    Select 
    		REPLACE(ISNULL(COL1,'N.A'),'CN=','') As CN1
    		, REPLACE(ISNULL(COL2,'N.A'),'CN=','') As CN2
    		, REPLACE(ISNULL(COL3,'N.A'),'OU=','') As OU1
    		, REPLACE(ISNULL(COL4,'N.A'),'OU=','') As OU2
    		, REPLACE(ISNULL(COL5,'N.A'),'OU=','') As OU3
    		, REPLACE(ISNULL(COL6,'N.A'),'DC=','') As DC1
    		, REPLACE(ISNULL(COL7,'N.A'),'DC=','') As DC2
    		, REPLACE(ISNULL(COL8,'N.A'),'DC=','') As DC3
    From	@t
    • Marked as answer by Murtuja Wednesday, April 13, 2011 4:16 AM
    Tuesday, April 12, 2011 11:22 AM
  • Thank you very much Sali and Steven. Both query worked fine.

     


    With Regards, Khokhar Murtuja S Database Administrator
    Wednesday, April 13, 2011 4:51 AM
  • Hi Sali,

    Yes,That was typo. the last line on my expected result should be ABC XYZ B C N.A D1 D2 N.A)



    With Regards, Khokhar Murtuja S Database Administrator
    Wednesday, April 13, 2011 4:53 AM