如何在sql server中填充多列， [英] How to stuff multiple columns in sql server ,

问题描述

Hii，



我想把多个列填充成一个，

Hii ,

I am trying to stuff multiple columns into one ,

select
    (CASE obj.Objective_Status
    WHEN 1 THEN 'Live'
    WHEN 2 THEN 'Draft'
    WHEN 3 THEN 'Completed'
    ELSE 'Obsolete'
    END) AS 'Objective_Status',

    COUNT(*) 'Percentage',

    (CASE obj.Objective_Status WHEN 1 THEN 2 WHEN 2 THEN 1 When 3 then 3 END) as OrderOfObjectives

FROM Objectives as obj with (nolock)
WHERE obj.Id IN (SELECT * FROM dbo.Split(
    (select STUFF((select ',' + ObjectivesIDsLive from @empObjectives for xml path ('')), 1, 1, '') ,
    STUFF((select ',' + ObjectiveIdsDraft from @empObjectives for xml path ('')), 1, 1, ''),
    STUFF((select ',' + ObjectivesIdsCompleted from @empObjectives for xml path ('')), 1, 1, ''))
, ','))
group by obj.Objective_Status
order BY OrderOfObjectives

这是我到目前为止所尝试过的，我尝试了一个专栏，其中工作正常，但没有multplie列..请建议

this is what i habe tried so far , i tried with one column and its working fine , but not with multplie columns .. please suggets

推荐答案

这样的事情应该有效：

Something like this should work:

SELECT
    CASE obj.Objective_Status
        WHEN 1 THEN 'Live'
        WHEN 2 THEN 'Draft'
        WHEN 3 THEN 'Completed'
        ELSE 'Obsolete'
    END AS 'Objective_Status',
    COUNT(*) 'Percentage',
    CASE obj.Objective_Status 
        WHEN 1 THEN 2 
        WHEN 2 THEN 1 
        WHEN 3 THEN 3 
    END as OrderOfObjectives
FROM 
    Objectives as obj with (nolock)
WHERE 
    Exists
    (
        SELECT 1
        FROM @empObjectives As EO
        CROSS APPLY dbo.Split(ObjectivesIDsLive, ',') As ID
        WHERE ID.value = obj.Id
    )
Or
    Exists
    (
        SELECT 1
        FROM @empObjectives As EO
        CROSS APPLY dbo.Split(ObjectivesIDsDraft, ',') As ID
        WHERE ID.value = obj.Id
    )
Or
    Exists
    (
        SELECT 1
        FROM @empObjectives As EO
        CROSS APPLY dbo.Split(ObjectivesIDsCompleted, ',') As ID
        WHERE ID.value = obj.Id
    )
GROUP BY 
    obj.Objective_Status
ORDER BY 
    OrderOfObjectives
;

您可能需要更改三个 WHERE ID.value = obj.Id 与 Split 函数返回的列名匹配的行。



最好更改 @empObjectives 表变量，以便不将ID组合为以逗号分隔的列表。

You might need to change the "value" column name on the three WHERE ID.value = obj.Id lines to match the column name returned from your Split function.

It would be better to change your @empObjectives table variable so that the IDs weren't combined as a comma-separated list.

这篇关于如何在sql server中填充多列，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！