[Solved] Query to return record value in column instead of row?

by 


SELECT c.ClientID,
   c.LastName,
   c.FirstName,
   c.MiddleName,
   CASE
       WHEN cudf.UserDefinedFieldFormatULink = '93fb3820-38aa-4655-8aad-a8dce8aede' THEN
           cudf.UDF_ReportValue AS 'DA Status'
       WHEN cudf.UserDefinedFieldFormatULink = '2144a742-08c5-4c96-b9e4-d6f1f56c76' THEN
           cudf.UDF_ReportValue AS 'FHAP Status'
       WHEN cudf.UserDefinedFieldFormatULink = 'c3d29be9-af58-4241-a02d-9ae9b43ffa' THEN
           cudf.UDF_ReportValue AS 'HCRA Status'
   END
INTO #Temp
FROM Client_Program cp
INNER JOIN client c
    ON c.ulink = cp.clientulink
INNER JOIN code_program p
    ON p.ulink = cp.programulink
INNER JOIN Code_System_State css
    ON c.ContactMailingStateUlink = css.ulink
INNER JOIN Code_ClientStatus ccs
    ON c.ClientStatusULink = ccs.ULink
INNER JOIN Client_UserDefinedField cudf
    ON c.ULink = cudf.ClientULink
       AND cp.ProgramStatusULink = '1' -- Open (active) program
       AND c.ClientStatusULink = '10000000' --Active client
       AND cp.programulink IN ( '7280f4a7-cd94-49be-86ad-a74421ff6f', '0a9b94a3-edd7-4918-b79c-bf2b20f9da',
                                '54f6c691-2eba-49e5-8380-85f5349bca', 'ed8c497d-d4fe-41d7-a218-4235fd0734',
                                '5be826f0-b3c3-4ebe-871d-4d20b56da5'
                              )
       AND cudf.UserDefinedFieldFormatULink IN (   '93fb3820-38aa-4655-8aad-a8dce8aede', -- DA Status
                                                   '2144a742-08c5-4c96-b9e4-d6f1f56c76'
                                               ); --FHAP Status

SELECT COALESCE(A.CilentID, B.ClientID, C.ClientID),LastName,
   FirstName, MiddleName, [DA Status], [FHAP Status], [HCRA Status] FROM 
(SELECT ClientID, LastName, FirstName, [DA Status] FROM #Temp WHERE [DA Status] IS NOT NULL) AS A FULL OUTER JOIN  
(SELECT ClientID, LastName, FirstName, [FHAP Status] FROM #Temp WHERE [FHAP Status] IS NOT NULL) AS B ON A.ClientID = B.ClientID FULL OUTER JOIN 
(SELECT ClientID, LastName, FirstName, [HCRA Status] FROM #Temp WHERE [HCRA Status] IS NOT NULL) AS C ON C.ClientID = A.ClientID

6

solved Query to return record value in column instead of row?