Rows into columns with subquery SQL Server 2008

I have a COTS database that stores 'custom' field information all in a single field (column). I have created a query to pull out the selected information for a single record but I need to be able to create columns out of each row extracted so sorting/summing can be accomplished.

Here's the query:

SELECT  DISTINCT CFV.Value, CFV.CustomRecordsetId, CFV.CustomFieldId, CF.Name, F.id, F.ShortFileName, F.DateFileOpen,
(select P.Name from [Amicus].[dbo].[People] P where (cf.name = 'MedLienLKUPMedProvider1' AND Convert(char,P.Id) = CFV.Value )) as ProviderName,
(select P.DefaultPhone from [Amicus].[dbo].[People] P where (cf.name = 'MedLienLKUPMedProvider1' AND Convert(char,P.Id) = CFV.Value )) as ProviderPhone
    FROM    [Amicus].[dbo].[CustomFieldValue] CFV
                               inner join [Amicus].[dbo].[CustomField] CF on cfv.customfieldId=cf.id
                               inner join [Amicus].[dbo].[CustomRecordset] CRS on CRS.Id=CFV.CustomRecordsetId
                               inner join [Amicus].[dbo].[File] F on F.Id=CRS.RecordId

    WHERE   F.Id = {?Pm-F.Id}
    AND     CF.Id = CFV.CustomFieldId
    AND     CF.CustomFieldTypeId <> 23 
                AND                               CRS.customrecordid = 8
                AND (cf.name = 'MedLienAcctNo' OR cf.name = 'MedLienAmountBilled1' OR cf.name = 'MedLienAmt' OR cf.name = 'MedLienAmtDue' OR cf.name = 'MedLienDateRec' OR cf.name = 'MedLienDiagAmt' OR cf.name = 'MedLienListTF1' OR cf.name = 'MedLienLKUPMedProvider1' OR cf.name = 'MedLienNotes' OR cf.name = 'MedLienServFromDate1' OR cf.name = 'MedLienServToDate1' OR cf.name = 'MedLienTreatAmt' OR cf.name = 'MedLienDescription') 

I need to transpose each of the cf.name lookups into its own column hea开发者_C百科ding AND keep the subquery, if possible.

The following works but doesn't include the subquery.

SELECT  

 MAX(CFV.CustomRecordsetId) AS RecordNo, MAX(CFV.CustomFieldId) AS CFId, MAX(F.id) AS FileId, MAX(F.ShortFileName) AS SFN, MAX(F.DateFileOpen) AS DateOpened,



    MAX(CASE WHEN cf.name = 'EACreditorLU' THEN CFV.Value END) AS Provider,
    MAX(CASE WHEN cf.name = 'EADebtAmtOwed' THEN CFV.Value END) AS AmtOwed,
    MAX(CASE WHEN cf.name = 'EADebtCategory' THEN CFV.Value END) AS Category,
    MAX(CASE WHEN cf.name = 'EADebtClassClaim' THEN CFV.Value END) AS Class,
    MAX(CASE WHEN cf.name = 'EADebtPer' THEN CFV.Value END) AS Per,
    MAX(CASE WHEN cf.name = 'EADebtStatus' THEN CFV.Value END) AS Status





FROM    [Amicus].[dbo].[CustomFieldValue] CFV
                           inner join [Amicus].[dbo].[CustomField] CF on cfv.customfieldId=cf.id
                           inner join [Amicus].[dbo].[CustomRecordset] CRS on CRS.Id=CFV.CustomRecordsetId
                           inner join [Amicus].[dbo].[File] F on F.Id=CRS.RecordId

WHERE   F.Id = {?pmFileId}
AND     CF.Id = CFV.CustomFieldId
AND     CF.CustomFieldTypeId <> 23 
            AND                               CRS.customrecordid = 24

            GROUP BY CFV.CustomRecordsetId

Any help would be greatly appreciated as I have run out of gas.

Thanks.

Make your subqueries into their own tables.
Here's a simple example of how to do this, which I think you can work out into your own solution. That way, you're not distracted from comprehending the idea.

SELECT
    User.Name,
    COUNT(LoginTimes.*)

FROM
    Users AS User,
    (SELECT LoginTimes.Time FROM LoginTimes WHERE LoginTimes.UserID = User.ID) AS LoginTimes

GROUP BY
    User.Name 

So now LoginTimes is a table that represents my subquery, which I can use anywhere else.
I could extract more than just the LoginTimes.Time column out of it, too. Hope that helps.

SQL requires all of it to be aggregated. Because the field referenced in the subquery table is part of the 'array', this defaults all the lookups in the subquery table to the max values. Perhaps I was doing something wrong but I don't think so.

I was able to create a view and then query it but can't seem to combine the two into a single query.

Here's the view query:

SELECT     MAX(CFV.CustomRecordsetId) AS RecordNo, MAX(CFV.CustomFieldId) AS CFId, MAX(F.Id) AS FileId, MAX(F.ShortFileName) AS SFN, MAX(F.DateFileOpen) AS DateOpened, 
MAX(CASE WHEN cf.name = 'MedLienAcctNo' THEN CFV.Value END) AS AcctNo, 
MAX(CASE WHEN cf.name = 'MedLienAmountBilled1' THEN CFV.Value END) AS AmtBilled, 
MAX(CASE WHEN cf.name = 'MedLienAmt' THEN CFV.Value END) AS LienAmt, 
MAX(CASE WHEN cf.name = 'MedLienAmtDue' THEN CFV.Value END) AS AmtDue, 
MAX(CASE WHEN cf.name = 'MedLienDateRec' THEN CFV.Value END) AS DateRec, 
MAX(CASE WHEN cf.name = 'MedLienDiagAmt' THEN CFV.Value END) AS DiagAmt, 
MAX(CASE WHEN cf.name = 'MedLienListTF1' THEN CFV.Value END) AS LienQ, 
MAX(CASE WHEN cf.name = 'MedLienLKUPMedProvider1' THEN CFV.Value END) AS MedProvider, 
MAX(CASE WHEN cf.name = 'MedLienNotes' THEN CFV.Value END) AS Notes, 
MAX(CASE WHEN cf.name = 'MedLienServFromDate1' THEN CFV.Value END) AS ServFrom, 
MAX(CASE WHEN cf.name = 'MedLienServToDate1' THEN CFV.Value END) AS ServTo, 
MAX(CASE WHEN cf.name = 'MedLienTreatAmt' THEN CFV.Value END) AS TreatAmt, 
MAX(CASE WHEN cf.name = 'MedLienDescription' THEN CFV.Value END) AS Description

FROM         dbo.CustomFieldValue AS CFV INNER JOIN
             dbo.CustomField AS CF ON CFV.CustomFieldId = CF.Id AND CFV.CustomFieldId = CF.Id INNER JOIN
             dbo.CustomRecordset AS CRS ON CRS.Id = CFV.CustomRecordsetId INNER JOIN
             dbo.[File] AS F ON F.Id = CRS.RecordId

WHERE     (F.Id = 27519) AND (CF.CustomFieldTypeId <> 23) AND (CRS.CustomRecordId = 8)
GROUP BY CFV.CustomRecordsetId

The query acting on the result of this view is:

SELECT P.Name, C.RecordNo, C.CFId, C.FileId, C.SFN, C.DateOpened, C.AcctNo, 
      C.AmtBilled, C.MedProvider
FROM    [dbo].[People] P,
    [dbo].[CRSet] C

WHERE   convert(char,P.Id)= C.MedProvider

If anyone can see a way to combine these two into a single query, I would appreciate the help.