How can I display rows values in columns sql server?

Question

I have this table structure for SheduleWorkers table:

    CREATE TABLE SheduleWorkers
    (
         [Name] varchar(250),
         [IdWorker] varchar(250),    
         [IdDepartment] int,
         [IdDay] int,
         [Day] varchar(250)
    );


INSERT INTO SheduleWorkers ([Name],  [IdWorker],  [IdDepartment], [IdDay], [Day])
values 
('Sam', '001', 5, 1, 'Monday'),
('Lucas', '002', 5, 2, 'Tuesday'),
('Maria', '003', 5, 1, 'Monday'),
('José', '004', 5, 3, 'Wednesday'),
('Julianne', '005', 5, 3, 'Wednesday'),
('Elisa', '006', 18, 1, 'Monday'),
('Gabriel', '007', 23, 5, 'Friday');

I need to display for each week day the names of workers in the department 5 that works in this day, like this:

MONDAY   TUESDAY   WEDNESDAY  THURSDAY  FRIDAY  SATURDAY
------   -------   ---------  --------  ------  -------
Sam       Lucas    Jose       
Maria              Julianne

How can I get this result, I accept suggestions, thanks.

Giorgi Nakeuri · Answer 1 · 2016-05-18T09:19:30.297

DECLARE @SheduleWorkers TABLE
    (
      [Name] VARCHAR(250) ,
      [IdWorker] VARCHAR(250) ,
      [IdDepartment] INT ,
      [IdDay] INT ,
      [Day] VARCHAR(250)
    );


INSERT  INTO @SheduleWorkers
        ( [Name], [IdWorker], [IdDepartment], [IdDay], [Day] )
VALUES  ( 'Sam', '001', 5, 1, 'Monday' ),
        ( 'Lucas', '002', 5, 2, 'Tuesday' ),
        ( 'Maria', '003', 5, 1, 'Monday' ),
        ( 'José', '004', 5, 3, 'Wednesday' ),
        ( 'Julianne', '005', 5, 3, 'Wednesday' ),
        ( 'Elisa', '006', 18, 1, 'Monday' ),
        ( 'Gabriel', '007', 23, 5, 'Friday' );

;
WITH    cte
          AS ( SELECT   Name ,
                        Day ,
                        ROW_NUMBER() OVER ( PARTITION BY Day ORDER BY [IdWorker] ) AS rn
               FROM     @SheduleWorkers
             )
    SELECT  [MONDAY] ,
            [TUESDAY] ,
            [WEDNESDAY] ,
            [THURSDAY] ,
            [FRIDAY] ,
            [SATURDAY]
    FROM    cte PIVOT( MAX(Name) FOR day IN ( [MONDAY], [TUESDAY], [WEDNESDAY],
                                              [THURSDAY], [FRIDAY], [SATURDAY] ) ) p

Output:

MONDAY  TUESDAY WEDNESDAY   THURSDAY    FRIDAY  SATURDAY
Sam     Lucas   José        NULL        Gabriel NULL
Maria   NULL    Julianne    NULL        NULL    NULL
Elisa   NULL    NULL        NULL        NULL    NULL

The main idea is row_number window function in the common table expression, which will give you as many rows as there are maximum duplicates across a day.

Thanks for your answer, your code works correctly to me ;-D – Esraa_92 May 18 '16 at 09:28 — Esraa_92, May 18 '16 at 09:28

Sandeep Kumar · Accepted Answer · 2016-05-18T09:02:43.653

2

You can use pivot for this. Please use below query for your problem. And use Partition.

SELECT  [Monday] , [Tuesday] , [Wednesday] , [Thursday] , [Friday], [SATURDAY]
FROM 
(SELECT [Day],[Name],RANK() OVER (PARTITION BY [Day] ORDER BY [Day],[Name]) as rnk
 FROM SheduleWorkers) p
 PIVOT(
Min([Name])
FOR [Day] IN
( [Monday] , [Tuesday] , [Wednesday] , [Thursday] , [Friday], [SATURDAY]  )
) AS pvt

edited May 18 '16 at 09:02

answered May 18 '16 at 08:46

Sandeep Kumar

1,172
1
9
22

Thank you very much for your answer, ;-D – Esraa_92 May 18 '16 at 09:14
Your Welcome :) @Esraa_92 – Sandeep Kumar May 18 '16 at 09:15
@Esraa_92, I don't mind but can you explain why did you accepted answer that was posted 10 min later than mine? Just want to know if I did something wrong :) – Giorgi Nakeuri May 18 '16 at 09:17
@Giorgi Nakeuri, Thanks for your help, your code is perfectly right, I accept this answer beacuse I'm not used to using recursive sentences beacuse I don´t have the command of CTE sentences, just that. This code is more easy to understand to me – Esraa_92 May 18 '16 at 09:27

gofr1 · Answer 3 · 2016-05-18T08:48:27.687

I suggest you to use PIVOT with dynamic SQL to get all date names you need:

DECLARE @column nvarchar(max),
        @sql nvarchar(max)

;WITH cte AS (
SELECT DATENAME(WEEKDAY,0) as [Day], 1 as [Level]
UNION ALL
SELECT DATENAME(WEEKDAY,[Level]), [Level] + 1
FROM cte
WHERE [Level] < 7
)

SELECT @column = STUFF((SELECT ','+QUOTENAME([Day]) FROM cte ORDER BY [Level]FOR XML PATH('')),1,1,'')

SELECT @sql = 
'SELECT '+@column+'
FROM (
    SELECT Name, [Day], RANK() OVER (PARTITION BY [Day] ORDER BY [Day],IdWorker) as rn
    FROM #SheduleWorkers
) as p
PIVOT
(
MAX(NAMe) FOR [Day] IN ('+@column+')
) as pvt'

EXEC(@sql)

Output:

Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
Sam     Lucas   Jose        NULL        Gabriel NULL        NULL
Maria   NULL    Julianne    NULL        NULL    NULL        NULL
Elisa   NULL    NULL        NULL        NULL    NULL        NULL

How can I display rows values in columns sql server?

3 Answers3