Post reply

Converting rows into columns

  • October 9, 2008 at 3:53 pm

    #199142

    Here is my scenario. We have an online form that gets filled by vendors and this is how gets stored. I need to create a report and prensent the rows as columns and join the formid to other tables, but i cannot figure out how to do it.

    Any help will be appreciated.

    Thank you.

    Table1

    FORMIDACTIONVALUE

    2587123AcceptCreditCard False

    2587123ActionNew Vendor

    2587123BusinessClassChoose One:

    2587123CityBellingham

    2587123Comment

    2587123ContactEmailsroach@ur.com

    2587123ContactNameAP

    2587123ContactPhone360-671-9414

    2587123CountryUS

    2587123DefaultAccount52015

    2587123DefaultCurrencyUS

    2587123DefaultPaymentMethodCodeCheck

    2587123DefaultSubAccount 11 Rental / Traffic Control

    2587123DefaultTermsNet 60

    2587123DoingBusinessAsA-1 Welding

    2587123Email

    2587123EmployeeNameSara Roach

    2587123Fax360-647-9284

    2587123HoldReason

    2587123IfOther

    2587123IndustryGroupChoose One:

    2587123IsNonPoTrue

    2587123IsPoFalse

    2587123IsTaxExemptFalse

    2587123ItemNumberLabor to form plate

    2587123LocationNumber921

    2587123LocationPhone360-647-7800

    2587123ManagerEmailbharring@ur.com

    2587123ManagerNameBrad Harrington

    2587123NameA-1 Welding

    2587123Note

    2587123Phone1360-671-9414

    2587123Phone2

    2587123Phone3

    2587123PostalCode98226

    2587123PurchasedFromRepairs and Maintenance - Equipment

    2587123PurchaseReason

    2587123RemitCity Bellingham

    2587123RemitCountryUSA

    2587123RemitNameA-1 Welding

    2587123RemitPostalCode98226

    2587123RemitStateWA

    2587123RemitStreet14000 Irongate Rd., Bldg A

    2587123RemitStreet2

    2587123RemitStreet3

    2587123StateWA

    2587123Street14000 Irongate Rd., Bldg A

    2587123Street2

    2587123Street3

    2587123SupplierTypeParts and Equip - PNE

    2587123TaxId1

    2587123TaxId2

    2587123TaxId3

    2587123TaxId4

    2587123TaxState

    2587123TinReasonCode

    2587123TinValidation

    2587123TinValidationStatus91-1579361

    2587123URL

    2587123VendorNumber

  • October 9, 2008 at 4:15 pm

    #883164

    Maynor, looks like you need to PIVOT. There is another recent thread on that subject. Check it out.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • October 9, 2008 at 4:30 pm

    #883169

    I need to pivot on some not all rows, I have search the sqlservercentral site for the answer but nothing yet..

    Thanks

  • October 9, 2008 at 5:02 pm

    #883172

    Can you do something like this:

    select formid, acceptCreditCard, city, Table2.anothercolumn

    from (select formid, action, value

    from table1) tblA PIVOT (Max(value) FOR action in ('AcceptCreditCard', 'City')) AS tblB

    INNER JOIN Table2 ON tblB.FormID = Table2.FormID

  • October 9, 2008 at 7:35 pm

    #883199

    maynor_ramirez (10/9/2008)

    I need to pivot on some not all rows, I have search the sqlservercentral site for the answer but nothing yet..

    Thanks

    http://www.sqlservercentral.com/articles/T-SQL/63681/

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply