Results 1 to 3 of 3

Thread: Pivot Table?

  1. #1
    Join Date
    Dec 2012
    Posts
    7

    Unanswered: Pivot Table?

    Hello,
    I have a report that pulls a patients insurance for each a visit. Each time a patient visits the hospital they get an AccountNumber. A patient could have multiple insurances for that visit. I am trying to get all policy numbers on the same line as the account number, thus decrease the number of lines for each account from 2 or 3 to one. I think I need to use a pivot table to do that but I can't seem to get it to work. Here is what I tried:



    SELECT BarVisits.AccountNumber, AdmInsuranceOrder.InsuranceOrderID,
    AdmInsuranceOrder.InsuranceID, AdmInsuredInfo.PolicyNumber

    FROM BarVisits INNER JOIN
    AdmInsuranceOrder ON BarVisits.VisitID = AdmInsuranceOrder.VisitID
    INNER JOIN
    AdmInsuredInfo ON AdmInsuranceOrder.VisitID = AdmInsuredInfo.VisitID
    AND AdmInsuranceOrder.InsuranceID = AdmInsuredInfo.InsuranceID

    ---------
    SELECT AdmInsuredInfo.PolicyNumber, [INS1], [INS2], [INS3]

    FROM BarVisits INNER JOIN
    AdmInsuranceOrder ON BarVisits.VisitID = AdmInsuranceOrder.VisitID
    INNER JOIN
    AdmInsuredInfo ON AdmInsuranceOrder.VisitID = AdmInsuredInfo.VisitID
    AND AdmInsuranceOrder.InsuranceID = AdmInsuredInfo.InsuranceID


    PIVOT

    (
    MAX(AdmInsuredInfo.PolicyNumber) FOR BarVisits.AccountNumber
    IN ([INS1], [INS2], [INS3])
    ) as pvt


    If I run that I get these errors:

    Msg 8156, Level 16, State 1, Line 10
    The column 'SourceID' was specified multiple times for 'pvt'.
    Msg 4104, Level 16, State 1, Line 10
    The multi-part identifier "AdmInsuredInfo.PolicyNumber" could not be bound.



    Any help appreciated,

    Thanks

    Evan

  2. #2
    Join Date
    Dec 2012
    Posts
    7
    Yikes!

    94 people viewed this and no help. Let me ask this differently then. Here is what I am getting:

    AccountNumber CoverageNumber PolicyNumber
    E12345 1 1234567
    E12345 2 WQE1234
    E24681 1 5246818
    E14789 1 4567891
    E14789 2 9516284
    E14789 3 142753F



    Her is what I need:

    AccountNumber PolicyNumber1 PolicyNumber2 PolicyNumber3
    E12345 1234567 WQE1234 NULL
    E24681 5246818 NULL NULL
    E14789 4567891 9516284 142753F

    Any suggestions?

    Thanks

    Evan
    Last edited by bassee; 02-18-13 at 15:35.

  3. #3
    Join Date
    Dec 2012
    Posts
    7
    I figured this out. I was making it way more complicated than it needed to be. Since I was really only looking for 2 types of insurance I sent each type to a different temporary table then joined the temporary tables to my main report.

    Thanks

    Evan

Posting Permissions

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