Esraa_92 Esraa_92 - 6 months ago 8
SQL Question

How can I join together this querys sql server?

SQL FIDDLE DEMO HERE

I have this table structure for Workers table:

CREATE TABLE Workers
(
[Name] varchar(250),
[IdWorker] varchar(250),
[work] varchar(250)
);

INSERT INTO Workers ([Name], [IdWorker], [work])
values
('Sam', '001', 'Director'),
('Julianne', '002', 'Recepcionist'),
('Jose', '003', 'Recepcionist');


What I want is to get for each job the name of workers separate by commas, like this:

Director Recepcionist
------- ------------
Sam Julianne, Jose


I tried to used this query:

DECLARE @rec VARCHAR(MAX)
SELECT @rec = COALESCE(@rec + ', ', '') + Name from
Workers where job = 'Recepcionist' SELECT @dir AS Recepcionist


And I got this result:

Recepcionist
------------
Julianne, Jose


This works only for one job, but I need to add more, so I tried then to use this query:

SELECT [Director] , [Recepcionist]
FROM
(SELECT [job], [Name],RANK() OVER (PARTITION BY [job] ORDER BY [job],[Name]) as rnk
FROM Workers ) p
PIVOT(
Min([Name])
FOR [job] IN
( [Director] , [Recepcionist] )
) AS pvt


And I get this result:

Director Recepcionist
-------- ------------
Sam Julianne
Jose


I need to get the results in the same row separate by commas, how can I combine the two querys?
I accept suggestions, thanks.

Answer

I am assuming in your example query you meant job to reference the work column. The following query should do the job as per your sql fiddle.

    SELECT  STUFF(
                    (
                        SELECT  ', ' + cast([Name] as varchar(max))
                        FROM    Workers
                        WHERE   [work] = 'Recepcionist'
                        FOR XML PATH('')
                    ), 1, 2, ''
            ) AS Recepcionist
            ,STUFF(
                (
                    SELECT  ', ' + cast([Name] as varchar(max))
                    FROM    Workers
                    WHERE   [work] = 'Director'
                    FOR XML PATH('')
                ), 1, 2, '') AS Director;
Comments