Verticall representation of data..

• Sachin Mahajan

  Newbie

  Points: 4

  More actions

  August 20, 2005 at 1:21 am

  #88768

  HI,

  I am struck with the problem. There is something wrong that I am doing I cannt figure out what. Please help me with this..

  I have the following table struture

  Table: Admin Table

  ID1   ID2   ID3   ID4

  1       2       3     4

  1       3        0     0

  1       2       0      4

   

  I actually want to see the results vertically i.e. something like this

  1

  2

  3

  4

  1

  3

  0 ....... and so on..

  Therefore I tried this approach of using full outer joins. But I am still not able to covert the rows to verticall representation..

  Please let me know what is that I am doing wrong..

  select ISNULL(a.ID1 , ISNULL(b.ID2 , ISNULL(c.ID3 , ISNULL(d.ID4 , 0)))),

  a.ID1 , b.ID2 , c.ID3  , d.ID4

  from AdminTable a

  full outer join AdminTable b

  on a.ID1 = b.ID1 and a.ID2 <> b.ID2

  full outer join AdminTable c

  on b.ID2 = c.ID2 and b.ID3 <> c.ID3

  full outer join AdminTable d

  on   c.ID3 = d.ID3 and c.ID4 <> d.ID1

   

  thanks

• Jeff Moden

  SSC Guru

  Points: 1004635

  More actions

  August 20, 2005 at 11:56 am

  #583763

  Does this do it for ya?

  SELECT 'ID1' AS IDName, ID1 AS IDValue FROM yourtable

  UNION ALL

  SELECT 'ID2' AS IDName, ID2 AS IDValue FROM yourtable

  UNION ALL

  SELECT 'ID3' AS IDName, ID3 AS IDValue FROM yourtable

  UNION ALL

  SELECT 'ID4' AS IDName, ID4 AS IDValue FROM yourtable

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)