Need Sql statement

[radha]

--Table
Create table tableName (Field1 int, Field2 int)

Table Data
Field1 Field2
1 10
2 10
3 10
1 20
2 20
3 30

I need Like a Sql statement like

1 10 (any separator) 20
2 10 20
3 10 30


thx a lot waiting for result



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Need Sql statement

radha (pottua_radha@y ahoo.co.in) writes:[color=blue]
> --Table
> Create table tableName (Field1 int, Field2 int)
>
> Table Data
> Field1 Field2
> 1 10
> 2 10
> 3 10
> 1 20
> 2 20
> 3 30
>
> I need Like a Sql statement like
>
> 1 10 (any separator) 20
> 2 10 20
> 3 10 30
>
>
> thx a lot waiting for result[/color]

It is not clear what result you are looking for. Do you want max or
min values? Or if there are 19 different values for Field1, do you want
all 19 values of Field2 in one row?

In the first case it's simple:

SELECT Field1, MIN(Field2), MAX(Field2)
FROM tableName
GROUP BY Field1

The recommendation for the second case is usually that you should to it
client-side, as SQL does not lend itself for this sort of thing. An
iterative solution is somewhat tedious to write, but it may be the best
way. I seem to recall that someone - I believe it was Anith Sen or
David Portas - a while back posted a set-based solution, which used an
auxilliary table with numbers from 1 to whatever number that is needed.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp