Prob: SELECT query

[sudhakar]

Hi..,

I am using SQL Server 7.0 SP2.
I've got a table x that has fields say:

id1 id2 a b c d e f

(each field name is separated by a space.
id1 + id2 is the primary key combination for this table)

If there are say 100 records in this table I can write a SELECT query like:

SELECT (a + b + c + d) AS Item
FROM x
WHERE id1 = 'my_value1' AND id2 = 'my_value2'

and I could get up to 16 records (say) using this query. The no. of records I get is variable.

My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor?
I'll be grateful for any help,

Thanks for your patience,

Cheers,

Sudhakar

[hemanth]

You need to have fixed number of records satisfying the condition; in your example 16; so you can get 16 columns output. In this case le me know I will give you the solution. (coz' its kinda big)
But if it is not 16 always, you need to convert (a + b + c + d) into char and keep concatinating it for all records.



------------
sudhakar at 8/2/00 6:09:34 PM

Hi..,

I am using SQL Server 7.0 SP2.
I've got a table x that has fields say:

id1 id2 a b c d e f

(each field name is separated by a space.
id1 + id2 is the primary key combination for this table)

If there are say 100 records in this table I can write a SELECT query like:

SELECT (a + b + c + d) AS Item
FROM x
WHERE id1 = 'my_value1' AND id2 = 'my_value2'

and I could get up to 16 records (say) using this query. The no. of records I get is variable.

My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor?
I'll be grateful for any help,

Thanks for your patience,

Cheers,

Sudhakar

[sudhakar]

Hi..,

Thanks for the quick reply.
Ofcourse while I am concatenating a + b + c + d it is a already a char variable as all a, b, c and d fields are char data type in the table x.
Like I said I can get up to a maximum of 16 records only. At any time it can always be <= 16 records but never > 16 records.
These 16 records are to be shown as 16 fields in a single record. Fields with no values can be shown as NULL.
I&#39;ll be grateful if you have any solution.

Cheers,

Sudhakar



------------
hemanth at 8/2/00 6:53:17 PM

You need to have fixed number of records satisfying the condition; in your example 16; so you can get 16 columns output. In this case le me know I will give you the solution. (coz&#39; its kinda big)
But if it is not 16 always, you need to convert (a + b + c + d) into char and keep concatinating it for all records.



------------
sudhakar at 8/2/00 6:09:34 PM

Hi..,

I am using SQL Server 7.0 SP2.
I&#39;ve got a table x that has fields say:

id1 id2 a b c d e f

(each field name is separated by a space.
id1 + id2 is the primary key combination for this table)

If there are say 100 records in this table I can write a SELECT query like:

SELECT (a + b + c + d) AS Item
FROM x
WHERE id1 = &#39;my_value1&#39; AND id2 = &#39;my_value2&#39;0

and I could get up to 16 records (say) using this query. The no. of records I get is variable.

My problem is to produce a single record that contains 16 Item fields. Can anyone advise me how to do this without using a cursor?
I&#39;ll be grateful for any help,

Thanks for your patience,

Cheers,

Sudhakar