Sql query help

[cazman]

Hi all,

Let's say I have this:

1 2 fruit 154
4 5 fruit 178
1 2 vegetable 456
4 5 vegetable 458
1 2 nuts 123
4 5 nuts 485


I would like to have the fallowing result:

1 2 (fruit/vegetable - nuts)
4 5 (fruit/vegetable - nuts)


thanks!

[Thomas R. Hummel]

Re: Sql query help

cazman wrote:[color=blue]
> Hi all,
>
> Let's say I have this:
>
> 1 2 fruit 154
> 4 5 fruit 178
> 1 2 vegetable 456
> 4 5 vegetable 458
> 1 2 nuts 123
> 4 5 nuts 485
>
>
> I would like to have the fallowing result:
>
> 1 2 (fruit/vegetable - nuts)
> 4 5 (fruit/vegetable - nuts)[/color]

SELECT '1 2 (fruit/vegetable - nuts)'
UNION
SELECT '4 5 (fruit/vegetable - nuts)'

If you want something else then you may want to provide a description
of your problem and preferably some sample SQL to create your table and
populate it with data.

-Tom.

[jennifer1970@hotmail.com]

Re: Sql query help


CREATE TABLE #T (A int, B int, C nvarchar(30), D int)

INSERT INTO #T VALUES (1, 2, 'fruit', 154)
INSERT INTO #T VALUES (4, 5, 'fruit', 178)
INSERT INTO #T VALUES (1, 2, 'vegetable', 456)
INSERT INTO #T VALUES (4, 5, 'vegetable', 458)
INSERT INTO #T VALUES (1, 2, 'nuts', 123)
INSERT INTO #T VALUES (4, 5, 'nuts', 485)

DECLARE @A int, @B int, @C nvarchar(20)
DECLARE @Comb nvarchar(100)

SET @Comb = ''
CREATE TABLE #T2 (A int, B int, C nvarchar(100))

DECLARE cSQL CURSOR FOR
SELECT a,b FROM #T
GROUP BY A,B

OPEN cSQL

FETCH NEXT FROM cSQL INTO @A, @B

WHILE @@fetch_status = 0
BEGIN
DECLARE cCOMBO CURSOR FOR
SELECT C FROM #T
WHERE A = @A and B = @B

OPEN cCOMBO

FETCH NEXT FROM cCOMBO INTO @C

WHILE @@fetch_status = 0
BEGIN
SET @Comb = @Comb + @C + ';'
FETCH NEXT FROM cCOMBO INTO @C
END

CLOSE cCOMBO
DEALLOCATE cCOMBO

INSERT INTO #T2 VALUES (@A, @B, @Comb)

FETCH NEXT FROM cSQL INTO @A, @B

SET @Comb = ''

END

CLOSE cSQL
DEALLOCATE cSQL

SELECT * FROM #T2

DROP TABLE #T
DROP TABLE #T2

[--CELKO--]

Re: Sql query help

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Your third nameless column in this personal
pseudo-code is a string; you cannot do math on strings.