Tutorial :T-SQL for changing data from one table and insert into another table



Question:

My base table is like:

ColumnA|ColumnB  ---------------     A   |  C1     A   |  C2     A   |  C3     B   |  C1     B   |  C3     C   |  C4  

I want to read records from the base table and write it into the below table:

ColumnA | C1 | C2 | C3 | C4  ----------------------------     A    | Y  |  Y | Y  | N     B    | Y  |  N | Y  | N     C    | N  |  N | N  | Y  

I don't want to use a cursor, but I don't know if that's possible or not.

Thanks


Solution:1

One (usually fast) way would be group by:

insert  NewTable (ColumnA, C1, C2, C3, C4)  select  ColumnA  ,       IsNull(max(case when ColumnB = 'C1' then 'Y' end), 'N')  ,       IsNull(max(case when ColumnB = 'C2' then 'Y' end), 'N')  ,       IsNull(max(case when ColumnB = 'C3' then 'Y' end), 'N')  ,       IsNull(max(case when ColumnB = 'C4' then 'Y' end), 'N')  from    OldTable  group by          ColumnA  

Another way is subqueries, like:

insert  NewTable (ColumnA, C1, C2, C3, C4)  select  src.ColumnA  ,       case when exists (select * from OldTable ot                             where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C1')                     then 'Y' else 'N' end  ,       case when exists (select * from OldTable ot                             where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C2')                     then 'Y' else 'N' end  ,       case when exists (select * from OldTable ot                             where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C3')                     then 'Y' else 'N' end  ,       case when exists (select * from OldTable ot                             where ot.ColumnA = src.ColumnA and ot.ColumnB = 'C4')                     then 'Y' else 'N' end  from    (          select  distinct ColumnA          from    OldTable          ) src  

Or, adapted from Chris Diver's answer, with pivot:

select  ColumnA  ,       case when C1 > 0 then 'Y' else 'N' end C1  ,       case when C2 > 0 then 'Y' else 'N' end C2  ,       case when C3 > 0 then 'Y' else 'N' end C3  ,       case when C4 > 0 then 'Y' else 'N' end C4  from    OldTable src  pivot   (          count(ColumnB)          for ColumnB IN ([C1], [C2], [C3], [C4])          ) pvt  


Solution:2

Have a look at the PIVOT command. From there you can do a INSERT INTO ... SELECT ...

SELECT ColumnA, [C1], [C2], [C3], [C4]   FROM (SELECT * FROM table) t   PIVOT  (   Count(ColumnB)   FOR ColumnB IN ([C1], [C2], [C3], [C4])  ) As Pvt   


Solution:3

assuming you can SELECT the information you like, then you can write the insert as the result of that selection.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »