Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: row info wanted in column (like cube)

    Hi

    I have a table called tblsample, where i have information stored row wise. Ther four quarter information is stored for many years. I want those information column wise for a given year.

    say

    select col1, col2 from tblsample where rqtr=1 and ryear = 2000
    select col1, col2 from tblsample where rqtr=2 and ryear = 2000
    select col1, col2 from tblsample where rqtr=3 and ryear = 2000
    select col1, col2 from tblsample where rqtr=4 and ryear = 2000

    i want information like

    for the Year 2000

    1 qtr 2 qtr 3 qtr 4 qtr


    How to acheive this in MSSQL 2000

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are several ways
    Code:
    select col1, sum(s1) as q1, sum(s2) as q2, sum(s3) as q3, sum(s4) as q4
    from (
    select col1, col2 as s1, 0 as s2, 0 as s3, 0 as s4
      from tblsample where rqtr=1 and ryear = 2000
    union all  
    select col1, 0, col2, 0, 0, 
      from tblsample where rqtr=2 and ryear = 2000
    union all  
    select col1, 0, 0, col2, 0 
      from tblsample where rqtr=3 and ryear = 2000
    union all  
    select col1, 0, 0, 0, col2 
      from tblsample where rqtr=4 and ryear = 2000
         ) dt
    group by col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    41
    sorry forgot to add one column.

    This exercise is for 50 employees

    means

    select empname, col1, col2 from tblsample where rqtr=1 and ryear = 2000
    select empname, col1, col2 from tblsample where rqtr=2 and ryear = 2000
    select empname, col1, col2 from tblsample where rqtr=3 and ryear = 2000
    select empname, col1, col2 from tblsample where rqtr=4 and ryear = 2000

    i need an output like
    ---------------------------------------------------------------
    Name 1 qtr 2 qtr 3 qtr 4 qtr
    ---------------------------------------------------------------
    1st emp
    2nd emp
    3rd emp
    50th emp
    ----------------------------------------------------------------

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which 50

    just rework the query i gave you to add the extra column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •