問題描述
我在創(chuàng)建查詢時需要幫助.我有客戶端表，其中包含唯一的客戶端信息 - 由其唯一的 ClientID 標識.我還有一個 Client_UserDefinedFields 表，其中包含客戶端的自定義數(shù)據(jù)值.它們通過 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 個自定義數(shù)據(jù)字段，我需要知道給定客戶端的值(如我的 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 表中有多個條目，我不想多次帶回同一個客戶端.我想把它放在一行中，每個狀態(tài)"字段都正確填充為一列.我該怎么做呢?這是我當前輸出的示例:
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模板網(wǎng)！