Click here to Skip to main content
14,691,424 members
Please Sign up or sign in to vote.
3.20/5 (2 votes)
See more:
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



this is what i habe tried so far , i tried with one column and its working fine , but not with multplie columns .. please suggets
Posted
Comments
jgakenhe 22-May-15 10:47am
   
If it works stuffing 1 column into another, but not with more than 1, then you probably need to do multiple subqueries all enclosed in the main SQL statement; similarly to a CTE.

SELECT * FROM (
--
)

1 solution

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
;

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.
   
Comments
Torakami 25-May-15 1:02am
   
Man you are the rock star ,,, thank you so much for your help , Its now working fine ..

only there was one mistake .. there is nothing called ID.value it should be Id.Items .

take a full stars
Richard Deeming 27-May-15 6:33am
   
That would be why I said:
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.
:)
Torakami 27-May-15 6:49am
   
yes , true.

anyways thanks a lot for your help ..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900