Looping thru records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looping thru records

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-04-01 : 04:34:31

Hi Guys

i have a table which has some common data i have to group this data. checking if we can avoid loop and get it thru with query.

sample go here...


CREATE TABLE [dbo].[Tablet1](
	[TableName] [varchar](200) NULL,
	[TableQry] [varchar](max) NULL,
	[TypeID] [int] NULL
) ON [PRIMARY]

GO

insert into Tablet1 values('A1','select * from A1 where id=1',1)
insert into Tablet1 values('B1','select * from B1 where id=1',10)
insert into Tablet1 values('B1','select * from B2 where id=2',11)
insert into Tablet1 values('B1','select * from B2 where id=3 and abc=21',12)
insert into Tablet1 values('C1','select * from C1 where id=11',1)

Out put should be ,which is for common table name get all the query with union all. 

A1	select * from A1 where id=1
B1	select * from B1 where id=1
	union all 
	select * from B2 where id=2
	union all
	select * from B2 where id=3 and abc=21	
C1	select * from C1 where id=11

Thanks

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-04-01 : 05:01:27

IS CTE a option if so how can i implement it.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 07:22:17

Select tablename,LEFT(T1.query,LEN(T1.query)-10)from
(
select tablename,
(Select [TableQry] + ' union all ' from Tablet1 where [Tablet1].TableName =T.TableName for xml path('') )as query
from [Tablet1]T
group by T.TableName
)T1

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-04-01 : 09:49:27

Thanks Ayamas, it works for physical table. but when i try for table variables gives a error.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 10:08:52

I didnt get any error

Declare @tbl table (
[TableName] [varchar](200) NULL,
[TableQry] [varchar](max) NULL,
[TypeID] [int] NULL
)

insert into @tbl values('A1','select * from A1 where id=1',1)
insert into @tbl values('B1','select * from B1 where id=1',10)
insert into @tbl values('B1','select * from B2 where id=2',11)
insert into @tbl values('B1','select * from B2 where id=3 and abc=21',12)
insert into @tbl values('C1','select * from C1 where id=11',1)

Select tablename,LEFT(T1.query,LEN(T1.query)-10) as query from
(
select tablename,
(Select [TableQry] + ' union all ' from @tbl t1 where t1.TableName =t2.TableName for xml path('') )as query
from @tbl t2
group by t2.TableName
)T1

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-04-02 : 09:50:53

i think i made some syntax mistake.. workes perfect fine. thanks once again

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 09:53:29

quote:
Originally posted by khalik

i think i made some syntax mistake.. workes perfect fine. thanks once again

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources