how to find no of row and column from a table?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  March 17, 2012 at 7:32 am

  #391817

  for example iam having a table with 3 rows and 3 column

  like this 1,1 1,2 1,3

  2,1 2,2 2,3

  3,1 3,2 3,2

  from this table if the member is selecting two space (2,2 2,3 3,2 3,3)

  it will be insert in the table as like this

  spid memberid row column

  1 1 2 2

  2 1 2 3

  3 1 3 2

  4 1 3 3

  if i need know no of blocks owned by member means

  i can easily do

  select count(row) from sp table where memberid=1

  it will find total block 4

  but i need how many no of column the member owned

  and how many no of row the member owned

  some one said just pick the maximum value

  of row and subtract that

  how can i find no of row not count of row

  and no of column

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  March 17, 2012 at 1:46 pm

  #1460897

  Having a bit of a problem with the broken English but I believe this might be what you're looking for.

  SELECT MemberID,

  RowsOwned = COUNT(DISTINCT [Row]),

  ColsOwned = COUNT(DISTINCT [Column])

  FROM dbo.yourtable

  GROUP BY MemberID

  ;

  --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)
  

• LutzM

  SSC Guru

  Points: 107049

  More actions

  March 17, 2012 at 2:50 pm

  #1460901

  Is this still the same issue as the one you posted a few days ago?

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]