n*********g
发帖数: 75 | 1 there are records in Database such as
1 A
2 A
2 B
3 C
4 B
I want to return a recordset that combine the value of
the second column if the value of the first column is the same,
like below
1 A
2 AB
3 C
4 B
how to implement it using sql statement? |
aw
发帖数: 127 | 2 use a self-join.
SELECT T1.lastname + isnull(T2.lastname,'')
FROM MYTABLE T1 LEFT JOIN MYTABLE T2
ON T1.firstname = T2.firstname AND T1.id < T2.id
【在 n*********g 的大作中提到】
: there are records in Database such as
: 1 A
: 2 A
: 2 B
: 3 C
: 4 B
: I want to return a recordset that combine the value of
: the second column if the value of the first column is the same,
: like below
: 1 A
|
y****i
发帖数: 5690 | 3 I think this way the solution is not scalable. What if there are records like
You will get
Instead of |
n*********g
发帖数: 75 | 4 doesn't work. the sql script returns:
1 A
2 AB
2 B
3 C
4 B
I don't want the third record....
btw: both columns are char
【在 aw 的大作中提到】
: use a self-join.
: SELECT T1.lastname + isnull(T2.lastname,'')
: FROM MYTABLE T1 LEFT JOIN MYTABLE T2
: ON T1.firstname = T2.firstname AND T1.id < T2.id
|
n****f
发帖数: 905 | 5 建议您用程序去实现.
在ORACLE 您可以用PL/SQL 去LOOP 整个表.
您的问题主要是同表中行数据和行数据的关系处理.
总之,我认为写一段小程序比较能简单解决您的问题.
【在 n*********g 的大作中提到】
: doesn't work. the sql script returns:
: 1 A
: 2 AB
: 2 B
: 3 C
: 4 B
: I don't want the third record....
: btw: both columns are char
|
n*********g
发帖数: 75 | 6 yeah, I agree it is easy to implement in program.
but it is not my decision, sigh.
【在 n****f 的大作中提到】
: 建议您用程序去实现.
: 在ORACLE 您可以用PL/SQL 去LOOP 整个表.
: 您的问题主要是同表中行数据和行数据的关系处理.
: 总之,我认为写一段小程序比较能简单解决您的问题.
|
n****f
发帖数: 905 | 7 OK, Please tell us more about your Table:
1. What DB you are using? Access, SQL or Oracle?
2. What is possible value for ID field? 1,2,3..... What is the MAX value?
3. What is possible value for DATA field? Just A,B and C or have more ?
4. How many record do you have?
5. Why you have to use One SQL statement to do that?
Is it possible let me CALL you?
【在 n*********g 的大作中提到】
: yeah, I agree it is easy to implement in program.
: but it is not my decision, sigh.
|
aw
发帖数: 127 | 8 还有个前面有人提到的问题，可不可能有：
2 A
2 B
2 C
。。。的情况？就说有未知个相同ID(不止2个)。
【在 n****f 的大作中提到】
: OK, Please tell us more about your Table:
: 1. What DB you are using? Access, SQL or Oracle?
: 2. What is possible value for ID field? 1,2,3..... What is the MAX value?
: 3. What is possible value for DATA field? Just A,B and C or have more ?
: 4. How many record do you have?
: 5. Why you have to use One SQL statement to do that?
: Is it possible let me CALL you?
|
