查询以在列而不是行中返回记录值?

问题描述

我在创建查询时需要帮助.我有客户端表，其中包含唯一的客户端信息 - 由其唯一的 ClientID 标识.我还有一个 Client_UserDefinedFields 表，其中包含客户端的自定义数据值.它们通过 ClientID 链接，并且在这个 Client_UserDefinedFields 表中可能有许多针对 ClientID 的记录.

I need assistance in creating a query. I have Client table that has unique client info - identified by their unique ClientID. I also have a Client_UserDefinedFields table that contains values of custom data for clients. They are linked via the ClientID and there may be many records for a ClientID in this Client_UserDefinedFields table.

我的情况是有 3 个自定义数据字段，我需要知道给定客户端的值(如我的 CASE 语句所示).我当前的查询将客户端带回 3 次(每个值一行)，我只想看到客户端一次(一行)并将这些值显示为列.不确定这是否可能或如何做到这一点.此外，当我尝试在我的选择中使用 CASE 语句时，我无法使用 AS 'fieldname' 来识别它 - 因为它在 AS 关键字上给了我一个错误.

My situation is that there are 3 custom data fields that I need to know the values for a given client (as shown by my CASE statement). My current query is bringing back the client 3 times (a row for each value) and I want to only see the client once (one row) and have these values shown as columns. Not sure if this is possible or how to that. Furthermore, when I tried using a CASE statement in my select, I cannot use AS 'fieldname' to identify it - since it's giving me an error on the AS keyword.

我当前的 SQL SELECT 语句示例

An example of my current SQL SELECT statement

    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
    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
'c3d29be9-af58-4241-a02d-9ae9b43ffa') --HCRA Status

同样，我的问题是，如果 Client_UserDefinedFields 表中有多个条目，我不想多次带回同一个客户端.我想把它放在一行中，每个状态"字段都正确填充为一列.我该怎么做呢?这是我当前输出的示例:

Again, my issue is that I don't want to bring back the same client multiple times if they had more than one entry in the Client_UserDefinedFields table. I'd like to bring this in one row with each "Status" field correctly populated as a columns. How do I do this? Here's a sample of my current output:

ClientID        LastName    FirstName   MiddleName  PCHP/HCH Status DA Status   FHAP Status         HCRA Status         
XXXXXXXXXXXX    River       Mike        Allan       Active          (null)      - None Selected -   (null)              
XXXXXXXXXXXX    River       Mike        Allan       Active          Active      (null)              (null)              
XXXXXXXXXXXX    River       Mike        Allan       Active          (null)      (null)              - None Selected -   

最终只想看到包含所有值的一条记录

Ultimately would like to see just the one record with all the values

ClientID        LastName    FirstName   MiddleName  PCHP/HCH Status DA Status   FHAP Status         HCRA Status         
XXXXXXXXXXXX    River       Mike        Allan       Active          Active      - None Selected -   - None Selected -

示例非常有用，因为我不是 SQL 专家.谢谢！

Examples are very helpful as I'm not a SQL guru. Thank you!

推荐答案

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

这篇关于查询以在列而不是行中返回记录值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！