Use of WHILE in SQL Query

Question

Post reply

Use of WHILE in SQL Query

amitabhssinha

SSC-Addicted

Points: 481
More actions
February 9, 2010 at 12:20 am

#216497

Hi there,
I want to use WHILE in my SQL Query, I think somewhere it is getting wrong so the rows in record set are getting repeated in the output.
I have 2 tables. In 1st table structure is like as:
Table1
(Id int PK,
Name VARCHAR(50),
Designation VARCHAR(16),
Address VARCHAR(200),
IsDelete BIT
)
2nd table structure is something like this as:
Table2(
Id INT FK(Id) references Table1,
Salary INT, CellNo BIGINT, Age INT
)
My Query is to get recordset from joining the two table on Id respectively. But, I am using WHILE to fetch the recordset from table2 in Comma Separated Value(CSV) format for Id matching to 2 tables.
Also, in Table2 there is n number of rows for single Id. (-Important-)
Suggest some possible way.
Thank you
Amitabh

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

Not sure why you think you need a WHILE. Try this:

SELECT [t1].[Id] AS [Id_Table1]

,[t1].[Name]

,[t1].[Designation]

,[t1].[Address]

,[t1].[IsDelete]

,[t2].[Id] AS [Id_Table2]

,[t2].[Salary]

,[t2].[CellNo]

,[t2].[Age]

FROM Table1 AS t1

JOIN Table2 AS t2

ON [t1].[Id] = [t2].[Id] ;

If there are records in table1 for which there is no match in table 2 and you want to retain those at well, then replace JOIN with LEFT JOIN.

If that's not what you want, then please elaborate on your requirements.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 2

if the Table2 has structure like as:

Table2

(

Id INT FK(Id) references TAble2,

Value

)

Now, in this table for Id=1,

Id Value

1 Salary

2 Age

3 Cell No

4 Address1

5 Address2

For Table2,

Id Name Designation IsDelete

1 AAA SSE True

2 ABA SSE True

I want to get the recordset on Table1.Id=Table2.Id so as to get the recordset for Id=1 in single row that too Table2 value in same column as CSV

Thank you

wschampheleer SSCertifiable Points: 5504 More actions · Answer 3

Please provide create table statements, sample data and an example of the output you would like to see for it.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 4

I want to use WHILE in my SQL Query, I think somewhere it is getting wrong so the rows in record set are getting repeated in the output.

I have 2 tables. In 1st table structure is like as:

CREATE TABLE Table1

(Id int PK,

Name VARCHAR(50),

Designation VARCHAR(16),

Address VARCHAR(200),

IsDelete BIT

)

2nd table structure is something like this as:

CREATE TABLE Table2(

Id INT FK(Id) references Table1,

VALUE VARCHAR(16)

)

SAMPLE DATA:

INSERT INTO Table1 VALUES(1,'AA','SSE','No.1,Blk.1', True)

INSERT INTO Table1 VALUES(2,'BB','JSE','No.2,Blk.2', True)

Id Name Designation Address IsDelete

--- ------ ---------- -------- ---------

1 AA SSE True

2 BB JSE No.2, Blk.2 True

INSERT INTO Table2 VALUES(1,'Salary')

INSERT INTO Table2 VALUES(1,'Age)

INSERT INTO Table2 VALUES(1,'Cell No')

Id Value

--- ------

1 Salary

1 Age

1 Cell No

. .

Sample Output:-

Id Name Designation Address Value IsDelete

--- ------ ---------- --------- ----- -------

1 AA SSE No.1,Blk.1 (Salary,Age,Cell No,...) True

My Query is to get recordset from joining the two table on Id respectively. But, I am using WHILE to fetch the recordset from table2 in Comma Separated Value(CSV) format for Id matching to 2 tables.

Also, in Table2 there is n number of rows for single Id. (-Important-)

Amitabh

muralikrishnap Newbie Points: 5 More actions · Answer 5

You need to use sql functions like stuff and for xml to get values from table 2 as csv.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

Something like this should work.

SELECTId, [Name], Designation, [Address], IsDelete,

SUBSTRING

(

REPLACE

(

REPLACE

(

( SELECT [VALUE] FROM Table2 T2 WHERE T1.Id = T2.Id FOR XML PATH('') ), '</VALUE>', ''

), '<VALUE>', ','

), 2, 80000

) AS [VALUE]

FROMTable1 T1

--Ramesh

wschampheleer SSCertifiable Points: 5504 More actions · Answer 7

SELECT [t1].[Id]

,[t1].[Name]

,[t1].[Designation]

,[t1].[Address]

,'(' + STUFF((SELECT ', ' + value

FROM [dbo].[Table2] t2

WHERE [t2].[Id] = t1.[Id]

FOR

XML PATH('')), 1, 2, '') + ')'

,[t1].[IsDelete]

FROM [dbo].[table1] t1 ;

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 8

muralikrishnap (2/9/2010)
You need to use sql functions like stuff and for xml to get values from table 2 as csv.

Thank you Ramesh, this is working fine.

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 9

wschampheleer (2/9/2010)
SELECT [t1].[Id] ,[t1].[Name] ,[t1].[Designation] ,[t1].[Address] ,'(' + STUFF((SELECT ', ' + value FROM [dbo].[Table2] t2 WHERE [t2].[Id] = t1.[Id] FOR XML PATH('')), 1, 2, '') + ')' ,[t1].[IsDelete]
FROM [dbo].[table1] t1 ;

ThankYou Willem, this working fine too.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 10

I recommend using the solution provided by Willem as it does not require two way REPLACE and also STUFF function performs better than the SUBSTRING function.

--Ramesh