Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anonmon
Starting Member
6 Posts |
 Posted - 2010-12-09 : 15:25:51
|
| I'm trying to perform a query using microsoft query.The query has several joins, with one table being joined containing interests of a particular person. That "intererst" table has 3 fields: ID, CODE, and date. That same table gathers the definition of CODE from yet another table.I want to perform a query that will take the top 2 interests of a particular id, and map the first one to an excel column interest1, and the 2nd interest of the same id to an excel column interest2.Ideas...Table Example:USER----IDLastFirstInterest----IDCODEDateInterestDescibed--------CODEDefinitionClass----IDYearMy Resulting columns would look like:USER.ID, User.Last, User.First, InterestDescribed.Definition (this column will contain the 1st interest for a particular ID), InterestDescribed.Definition (this column will display the 2nd interest), Class.YearHope that makes sense...Thanks |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-09 : 23:01:15
|
| Can you show us what you have tried ? |
 |
|
|
anonmon
Starting Member
6 Posts |
Posted - 2010-12-10 : 00:26:06
|
| Here you go...The full SQL querySELECT STVTERM.STVTERM_DESC, SPRIDEN.SPRIDEN_LAST_NAME, SPRIDEN.SPRIDEN_FIRST_NAME, SPRIDEN.SPRIDEN_MI, SPRIDEN.SPRIDEN_ID, SPRADDR.SPRADDR_STREET_LINE1, SPRADDR.SPRADDR_STREET_LINE2, SPRADDR.SPRADDR_STREET_LINE3, SPRADDR.SPRADDR_CITY, SPRADDR.SPRADDR_STAT_CODE, SPRADDR.SPRADDR_ZIP, SPRTELE.SPRTELE_PHONE_AREA, SPRTELE.SPRTELE_PHONE_NUMBER, SPRTELE.SPRTELE_PHONE_EXT, SPRADDR.SPRADDR_PHONE_AREA, SPRADDR.SPRADDR_PHONE_EXT, SARADAP.SARADAP_APPL_DATE, STVAPST.STVAPST_DESC, STVAPDC.STVAPDC_DESC, SARAPPD.SARAPPD_APDC_DATE, STVADMT.STVADMT_DESC, STVSTYP.STVSTYP_DESC, STVMAJR.STVMAJR_DESC, STVINTS.STVINTS_DESC, STVSBGI.STVSBGI_DESC, SORHSCH.SORHSCH_PERCENTILE, SORHSCH.SORHSCH_CLASS_RANK, SORHSCH.SORHSCH_CLASS_SIZE, SORHSCH.SORHSCH_GPAFROM SATURN.SARADAP SARADAP, SATURN.SARAPPD SARAPPD, SATURN.SORHSCH SORHSCH, SATURN.SORINTS SORINTS, SATURN.SPRADDR SPRADDR, SATURN.SPRIDEN SPRIDEN, SATURN.SPRTELE SPRTELE, SATURN.STVADMT STVADMT, SATURN.STVAPDC STVAPDC, SATURN.STVAPST STVAPST, SATURN.STVINTS STVINTS, SATURN.STVMAJR STVMAJR, SATURN.STVSBGI STVSBGI, SATURN.STVSTYP STVSTYP, SATURN.STVTERM STVTERMWHERE SARADAP.SARADAP_PIDM = SPRIDEN.SPRIDEN_PIDM AND SARAPPD.SARAPPD_PIDM = SPRIDEN.SPRIDEN_PIDM AND SARAPPD.SARAPPD_APDC_CODE = STVAPDC.STVAPDC_CODE AND SARADAP.SARADAP_ADMT_CODE = STVADMT.STVADMT_CODE AND STVSTYP.STVSTYP_CODE = SARADAP.SARADAP_STYP_CODE AND STVMAJR.STVMAJR_CODE = SARADAP.SARADAP_MAJR_CODE_1 AND SORINTS.SORINTS_PIDM = SPRIDEN.SPRIDEN_PIDM AND STVINTS.STVINTS_CODE = SORINTS.SORINTS_INTS_CODE AND SORHSCH.SORHSCH_PIDM = SPRIDEN.SPRIDEN_PIDM AND STVSBGI.STVSBGI_CODE = SORHSCH.SORHSCH_SBGI_CODE AND SARADAP.SARADAP_TERM_CODE_ENTRY = STVTERM.STVTERM_CODE AND SPRADDR.SPRADDR_PIDM = SPRIDEN.SPRIDEN_PIDM AND SARADAP.SARADAP_APST_CODE = STVAPST.STVAPST_CODE AND SPRTELE.SPRTELE_PIDM = SPRIDEN.SPRIDEN_PIDM AND ((SPRIDEN.SPRIDEN_ID='000012345') AND (SARAPPD.SARAPPD_TERM_CODE_ENTRY='201210') AND (SPRADDR.SPRADDR_ATYP_CODE='PR') AND (SPRTELE.SPRTELE_TELE_CODE='PR'))This query returns four records and so in excel, 4 rows of information. All four rows of information are identical except for the STVINTS.STVINTS_DESC field. There are four different values for the SPRIDEN.SPRIDEN_ID of 000012345, 1. Golf, 2. Soccer, 3. Basketball, 4. Football... What I want excel to do is take the 1st value Golf and place it in a column labeled Interest1, and take the second value of Soccer and place it in the column labeled Interest2 all on the same row. I would only have 1 row of information displayed, instead of 4. It's something like a TOP command, but I'm "topping" the 1st two records out of a joined table.Hope this helps... |
|
|
|
|
|
|
|
|
Excel Microsoft Query