August 4, 2010 at 9:14 am
here looks my source table
declare @test-2 table (Name varchar(3), userid char(5))
insert into @test-2
SELECT 'aaa', 'sjohn' UNION ALL
SELECT 'bbb', 'sjohn' UNION ALL
SELECT 'bbb', 'pkevin' UNION ALL
SELECT 'ccc', 'lsmith' UNION ALL
SELECT 'ccc', 'enobili' UNION ALL
SELECT 'ddd', 'lsmith' UNION ALL
SELECT 'ddd', 'rlynn'
and i have to create a flatfile from that table which looks like
#name
name,userid
aaa,sjohn
#name
name,userid
bbb,sjohn
bbb,pkevin
#name
name,userid
ccc,lsmith
ccc,enobili
#name
name,userid
ddd,lsmith
ddd,rlynn
rt now i have my flat file like the below which is not the desired one
#name
name,userid
aaa,sjohn
bbb,sjohn
bbb,pkevin
ccc,lsmith
ccc,enobili
ddd,lsmith
ddd,rlynn
can anybody plss help me how to achieve tha desired flatfile which should be like:
#name
name,userid
aaa,sjohn
#name
name,userid
bbb,sjohn
bbb,pkevin
#name
name,userid
ccc,lsmith
ccc,enobili
#name
name,userid
ddd,lsmith
ddd,rlynn
August 4, 2010 at 9:59 am
Try this:
declare @test-2 table (Name varchar(30), userid char(50))
insert into @test-2
SELECT 'aaa', 'sjohn' UNION ALL
SELECT 'bbb', 'sjohn' UNION ALL
SELECT 'bbb', 'pkevin' UNION ALL
SELECT 'ccc', 'lsmith' UNION ALL
SELECT 'ccc', 'enobili' UNION ALL
SELECT 'ddd', 'lsmith' UNION ALL
SELECT 'ddd', 'rlynn'
;with rn
as
(
select name, 2 grp, DENSE_RANK() OVER(ORDER BY name) rnk
from @test-2
)
,uAll
AS
(
select t.*, rn.grp, rn.rnk
from @test-2 t
join rn on rn.Name = t.Name
union
select l1.Name, l1.userid, l1.grp, rn.rnk
from (values ('#name','', 0)) l1(Name,userid,grp)
cross join rn
union
select l1.Name, l1.userid, l1.grp, rn.rnk
from (values ('name','userid', 1)) l1(Name,userid,grp)
cross join rn
)
select name, userid
from uAll order by rnk, grp
Output into text file without column headers...
August 4, 2010 at 2:00 pm
HI Eugene Elutin,
Thanks for the reply, but its getting an error saying "incorrect syntax near keyword 'values'"
August 4, 2010 at 4:36 pm
I tried adding as after values but it did not worked can anybody help how the error solved plss
August 5, 2010 at 2:09 am
It sounds like you posted in the wrong forum then, because this solution works for SQL Server 2008. If you're using a different version of SQL Server, you should've posted in the appropriate forum.
August 5, 2010 at 2:35 am
I don't know which version of sqlserver you have. Try:
;with rn
as
(
select name, 2 grp, DENSE_RANK() OVER(ORDER BY name) rnk
from @test-2
)
,uAll
AS
(
select t.*, rn.grp, rn.rnk
from @test-2 t
join rn on rn.Name = t.Name
union
select l1.Name, l1.userid, l1.grp, rn.rnk
from (select '#name' Name,'' userid, 0 grp) l1
cross join rn
union
select l1.Name, l1.userid, l1.grp, rn.rnk
from (select 'name' Name,'userid' userid, 1 grp) l1
cross join rn
)
select name, userid
from uAll order by rnk, grp
August 5, 2010 at 10:25 am
Thankyou very much, not sure why the other one did not worked though i have sqlserver 2008....
August 5, 2010 at 10:51 am
I also recommend to change
select name, 2 grp, DENSE_RANK() OVER(ORDER .....
to:
select DISTINCT name, 2 grp, DENSE_RANK() OVER(ORDER ...
the above should make smaller cross joins
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply