Link to home
Start Free TrialLog in
Avatar of rares_dumitrescu
rares_dumitrescu

asked on

SQL Advanced SELECT Statement

Hello,

I have 2 tables like the following:

table1
---------------
name
xxx
yyy

table2
-------------------
name    properties
xxx           file1
xxx           file2
yyy           file3
yyy           file2
yyy           file1


I need a select statement that will prompt me the following output:

1. xxx    file1
             file2        --this value needs to be exactly like this, on the next line

2. yyy   file3
            file2
            file1


thx
Avatar of chapmandew
chapmandew
Flag of United States of America image

select * from table2
Avatar of rares_dumitrescu
rares_dumitrescu

ASKER

:)

I think you got the wrong picture.

Let`s say I have only table 2

I need a select that will prompt me:


column1  column2  column3
1.             xxx            file1
                                 file2
2.             yyy            file3
                                 file2
                                 file1
You need to define which row is the first one in each group.  I presume you're implying to use the first one from each name but you can't count on ordering like that in a relational database.  If you were to say, use the first one from each name alphabetically it would be something like this

Select
    case
      when t2.properties = t3.themax then name
      else Space(Len(t2.name)) End As name2,
      t2.properties
  From table2 as t2 Join (
      Select name, max(properties) as themax
        from table2
        group by name ) As t3
    On t2.name = t3.name
nothing in sql can guarantee you that output ...

you appear to want the xxx rows in ascending order of
properties file1 then file2
whilst for the yyy rows you want the descending property order
file3 then file 2 then file1

you haven't indicated any other relationships which would allow the
data to be generically ordered in a consistent manner...

explain why you have the two tables...
you would seem to only require table2 to actually get and display the data.

if you want to hide the subsequent name values then that normally considered
to be a client application responsibility...
SQL is primarily concerned with manipluating the sets of data in your tables,
and isn't really bothered about how the results should be presented...



 
ASKER CERTIFIED SOLUTION
Avatar of mastoo
mastoo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have done in the end. you need to use multiple cursors to have such an output. The second cursor will be based on a WHERE condition with a variable from the first cursor...and the you print it