Geek Answers Handbook

How can I display row values ​​in sql server columns?

SQL FIDDLE DEMO HERE

I have this table for the 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 on each working day the names of workers in department 5 who work on that day, for example:

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

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

+4
source share
3 answers

You can use a support element for this. Please use the below request for your problem. And use the section.

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
+2
source 
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

Conclusion:

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

- row_number , , .

+5

I suggest you use PIVOT with dynamic SQL to get all the 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)

Conclusion:

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
+1
source

More articles:


All Articles