subquery error

[magmo]

Hi


I try to run the following query..

SELECT
(SELECT DISTINCT Artno, L, D, CreatedBy FROM dbo.tbl_Art WHERE (L = N'SE')) AS SE,
(SELECT DISTINCT Artno, L, D, CreatedBy FROM dbo.tbl_Art WHERE (L = N'GB')) AS GB
FROM  dbo.tbl_Art

But I get a error message telling me..."only one expression can be specified in the selected list when the subquery is not introduced with exsits"


What do I need to change to get this to work?


Regards

 

[r937]

what you need to change depends on what you actually want

SELECT DISTINCT Artno, L, D, CreatedBy
FROM dbo.tbl_Art
WHERE L IN ( N'SE', N'GB')

the DISTINCT should probably be removed too

r937.com | rudy.ca

 

[magmo]

Hi


If I run a SELECT * FROM Artno I get this...

Artno L
123 GB
123 ES
456 GB
789 FR

But I would like to have it like this...

ES GB FR

123 123
456

789




Hope this make sence, is this possible?

 

[r937]

SELECT max(case when L = 'ES'
                then cast(Artno as varchar)
                else ''
            end) as ES   
     , max(case when L = 'GB'
                then cast(Artno as varchar)
                else ''
            end) as GB   
    , max(case when L = 'FR'
                then cast(Artno as varchar)
                else ''
            end) as FR   
  FROM dbo.tbl_Art 
group
    by Artno

r937.com | rudy.ca

 

[magmo]

Hi


Just perfect! Thank you so much.


Regards