Posts

Showing posts from June 8, 2012

Microsoft SQL Server 2005:Change string value into multiple rows

Sample Table based on select query. select 1 r,'abcd' String union all select 2,'efgh' union all select 3,'ijkl' union all select 4,'mnop' union all select 5,'qrst' union all select 6,'uvwx' union all select 7,'yz' Create #temp table. select * into #temp from ( select 1 r,'abcd' String union all select 2,'efgh' union all select 3,'ijkl' union all select 4,'mnop' union all select 5,'qrst' union all select 6,'uvwx' union all select 7,'yz') a select *from #temp Output r    String 1    abcd 2    efgh 3    ijkl 4    mnop 5    qrst 6    uvwx 7    yz ;WITH L0 AS (SELECT 1 AS c UNION ALL SELECT 1), L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B), L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B), L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B), L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B), Nums