need output where same column has more than one pirce of data

2 Dec 2005 8:45 PM

Chesster

CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))

INSERT INTO test values(111,'a12','a')
INSERT INTO test values(111,'d33','a')
INSERT INTO test values(222,'a25','a')
INSERT INTO test values(222,'g21','e')
INSERT INTO test values(333,'a65','a')
INSERT INTO test values(333,'a64','e')
INSERT INTO test values(444,'w11','f')
INSERT INTO test values(555,'a41','a')
INSERT INTO test values(555,'r99','a')
INSERT INTO test values(555,'a76','e')

I need to output the records where the same material has an 'A' as the first
character in col1 and col2 has both an 'A' and an 'E'. So the output should
look like this:

Material col1 col2
333          a65   a
333          a64   e
555          a41   a
555          a76   e

TIA

2 Dec 2005 8:55 PM

Aaron Bertrand [SQL Server MVP]

Without better specs, my first guess is:

SELECT material, MIN(col1), col2
    FROM test
    GROUP BY material, col2
    ORDER BY material, col2

Now, you say you want the output to be those 4 rows. Are those the ONLY
rows that should be returned? If so, why are you eliminating the rows where
material = 111, for example?

Please see http://www.aspfaq.com/5006 for some information on providing
requirements that make follow-up questions, and a delayed solution, much
less likely...

A

Show quote

"Chesster" <Chess***@discussions.microsoft.com> wrote in message
news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@microsoft.com...
> CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
>
> INSERT INTO test values(111,'a12','a')
> INSERT INTO test values(111,'d33','a')
> INSERT INTO test values(222,'a25','a')
> INSERT INTO test values(222,'g21','e')
> INSERT INTO test values(333,'a65','a')
> INSERT INTO test values(333,'a64','e')
> INSERT INTO test values(444,'w11','f')
> INSERT INTO test values(555,'a41','a')
> INSERT INTO test values(555,'r99','a')
> INSERT INTO test values(555,'a76','e')
>
> I need to output the records where the same material has an 'A' as the
> first
> character in col1 and col2 has both an 'A' and an 'E'. So the output
> should
> look like this:
>
> Material col1 col2
> 333          a65   a
> 333          a64   e
> 555          a41   a
> 555          a76   e
>
> TIA
>

2 Dec 2005 9:11 PM

Chesster

Yes, those are the only rows that should be returned.

Rows 111 should not be in the output because col2 does not have both an 'A'
and an 'E'.

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> Without better specs, my first guess is:
>
> SELECT material, MIN(col1), col2
>     FROM test
>     GROUP BY material, col2
>     ORDER BY material, col2
>
> Now, you say you want the output to be those 4 rows. Are those the ONLY
> rows that should be returned? If so, why are you eliminating the rows where
> material = 111, for example?
>
> Please see http://www.aspfaq.com/5006 for some information on providing
> requirements that make follow-up questions, and a delayed solution, much
> less likely...
>
> A
>
>
> "Chesster" <Chess***@discussions.microsoft.com> wrote in message
> news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@microsoft.com...
> > CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
> >
> > INSERT INTO test values(111,'a12','a')
> > INSERT INTO test values(111,'d33','a')
> > INSERT INTO test values(222,'a25','a')
> > INSERT INTO test values(222,'g21','e')
> > INSERT INTO test values(333,'a65','a')
> > INSERT INTO test values(333,'a64','e')
> > INSERT INTO test values(444,'w11','f')
> > INSERT INTO test values(555,'a41','a')
> > INSERT INTO test values(555,'r99','a')
> > INSERT INTO test values(555,'a76','e')
> >
> > I need to output the records where the same material has an 'A' as the
> > first
> > character in col1 and col2 has both an 'A' and an 'E'. So the output
> > should
> > look like this:
> >
> > Material col1 col2
> > 333          a65   a
> > 333          a64   e
> > 555          a41   a
> > 555          a76   e
> >
> > TIA
> >
>
>
>

2 Dec 2005 9:25 PM

Aaron Bertrand [SQL Server MVP]

SET NOCOUNT ON;
GO

CREATE TABLE test
(
material INT,
col1 VARCHAR(3),
col2 VARCHAR(1)
);
GO

INSERT test SELECT 111,'a12','a';
INSERT test SELECT 111,'d33','a';
INSERT test SELECT 222,'a25','a';
INSERT test SELECT 222,'g21','e';
INSERT test SELECT 333,'a65','a';
INSERT test SELECT 333,'a64','e';
INSERT test SELECT 444,'w11','f';
INSERT test SELECT 555,'a41','a';
INSERT test SELECT 555,'r99','a';
INSERT test SELECT 555,'a76','e';

SELECT t.material, MIN(t.col1), t.col2
FROM test t
WHERE
LEFT(t.col1,1) = 'a'
AND
(
(col2 = 'a' AND EXISTS
(
   SELECT 1 FROM test tA
    WHERE tA.material = t.material
    AND LEFT(tA.col1,1) = 'a'
    AND col2 = 'e'
))
OR
(col2 = 'e' AND EXISTS
(
   SELECT 1 FROM test tB
    WHERE tB.material = t.material
    AND LEFT(tB.col1,1) = 'a'
    AND col2 = 'a'
))
)
GROUP BY material, col2
ORDER BY material, col2;
GO

DROP TABLE test;
GO

Show quote

"Chesster" <Chess***@discussions.microsoft.com> wrote in message
news:854D3331-2645-4F01-8204-86AABF15AD91@microsoft.com...
> Yes, those are the only rows that should be returned.
>
> Rows 111 should not be in the output because col2 does not have both an
> 'A'
> and an 'E'.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> Without better specs, my first guess is:
>>
>> SELECT material, MIN(col1), col2
>>     FROM test
>>     GROUP BY material, col2
>>     ORDER BY material, col2
>>
>> Now, you say you want the output to be those 4 rows. Are those the ONLY
>> rows that should be returned? If so, why are you eliminating the rows
>> where
>> material = 111, for example?
>>
>> Please see http://www.aspfaq.com/5006 for some information on providing
>> requirements that make follow-up questions, and a delayed solution, much
>> less likely...
>>
>> A
>>
>>
>> "Chesster" <Chess***@discussions.microsoft.com> wrote in message
>> news:1DA8479F-3093-40D0-B63D-C2B7630D0DA8@microsoft.com...
>> > CREATE TABLE test (material int, col1 varchar(3), col2 varchar(1))
>> >
>> > INSERT INTO test values(111,'a12','a')
>> > INSERT INTO test values(111,'d33','a')
>> > INSERT INTO test values(222,'a25','a')
>> > INSERT INTO test values(222,'g21','e')
>> > INSERT INTO test values(333,'a65','a')
>> > INSERT INTO test values(333,'a64','e')
>> > INSERT INTO test values(444,'w11','f')
>> > INSERT INTO test values(555,'a41','a')
>> > INSERT INTO test values(555,'r99','a')
>> > INSERT INTO test values(555,'a76','e')
>> >
>> > I need to output the records where the same material has an 'A' as the
>> > first
>> > character in col1 and col2 has both an 'A' and an 'E'. So the output
>> > should
>> > look like this:
>> >
>> > Material col1 col2
>> > 333          a65   a
>> > 333          a64   e
>> > 555          a41   a
>> > 555          a76   e
>> >
>> > TIA
>> >
>>
>>
>>

2 Dec 2005 9:48 PM

Alexander Kuznetsov

select * from #test where [material] in(
select ta.[material] from (
    select [material] from #test where col1 like 'a%' and col2='a') ta
join (
    select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e')

material    col1 col2
----------- ---- ----
333         a65 a
333         a64 e
555         a41 a
555         r99 a
555         a76 e

I see that
555         r99 a
is not present in your sample output. I don't see why it does not
belong in the result set

2 Dec 2005 9:53 PM

Aaron Bertrand [SQL Server MVP]

> I see that
> 555 r99 a
> is not present in your sample output. I don't see why it does not
> belong in the result set

Because the first letter of col1 != 'a'

2 Dec 2005 10:01 PM

Alexander Kuznetsov

thanks Aaron.

select * from #test t1
where col1 like 'a%'
and col2 in('a','e')
and (select count(*) from #test t2
where t1.[material] = t2.[material]
and t2.col1 like 'a%'
and t2.col2 in('a','e') and t2.col1<>t1.col1)>0

material    col1 col2
----------- ---- ----
333         a65 a
333         a64 e
555         a41 a
555         a76 e

(4 row(s) affected)

3 Dec 2005 3:06 PM

amish

select * from #test where [material] in(
select ta.[material] from (
select [material] from #test where col1 like 'a%' and col2='a') ta
join (
select [material] from #test where col1 like 'a%' and col2='e') te
on ta.[material] = te.[material]
)
and col2 in('a','e') and col1 like 'a%'

just added col1 like 'a%' at the end.

Regards