asked
Duplicate pivot colunns
I am generating a pivot from a table and I have one column that I am pivoting that contains more than one value in the data. As such: ![alt text][1] Note the two Booking Numbers. Pivot only shows one Booking Number: ![alt text][2] [1]: /storage/temp/964-sqlkeeper+-+remote+desktop+connection+manager+v2.gif [2]: /storage/temp/965-sqlkeeper+-+remote+desktop+connection+manager+v3.gif TSQL to produce both: select MasterBillOfLading_Id , internalId , PODReceived , Status , type , text , ReferenceNumbers_Id from vw_RefNumbers_by_MBOL SELECT * FROM (select MasterBillOfLading_Id , internalId , PODReceived , Status , type , text , ReferenceNumbers_Id from vw_RefNumbers_by_MBOL) AS D PIVOT(max(text) FOR type IN([AP Vendor Number],[Booking Number] ,[Business Group],[Customer Acct Number],[Load Number],[PRO],[SCAC])) AS P where [Booking Number] in ('103246683', '103246482') order by [Load Number] Anyone know how to gte both occurances of the Booking number in this case? TIA
The problem appears to be with the line: PIVOT(max(text) FOR type IN([Booking Number] --in specific, the MAX aggregate. I can substitute MIN and get the other number. However, I have some records that have 5-6 Booking numbers - min and max is not gonna cut it for these. Anyone know a way around the aggregation requirement in PIVOT?
Lol Thomas, that would be helpful, huh? I would like to see output similar to the second image(pivot data) but without having lost multiple Booking numbers. In other words, pivot the data, but return multiple rows where the result set contains more than one occurrence of any given pivoted column's data element. It would appear that the aggregate is the culprit here, and perhaps by virtue of the fact that there are multiple like objects in the result set, a pivot may not be indicated in this case. Perhaps I should just settle for breaking this up into smaller usable results.
It sounds to me as though you want a PIVOT to do most of the heavy lifting, and then join that with a query to get the individual booking numbers...
Ha! worked like a champ! Thanks Thomas! Sometimes I guess I over think things. ![alt text][1] ![alt text][2] [1]: /storage/temp/969-sqlkeeper+-+remote+desktop+connection+manager+v4.gif [2]: /storage/temp/970-sqlkeeper+-+remote+desktop+connection+manager+v5.gif
14 People are following this question.
