Pivot Table

Question

Post reply

Pivot Table

mrsiro

Old Hand

Points: 376
More actions
August 27, 2019 at 2:45 pm

#3674751

Hello guys, i have table as yellow area. How to query get result as blue area. Somebody help me please. Thank very much.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24401 More actions · Answer 1

How many unique "Work" items are there?

If there are 3 names, must they be "Name1 & Name2 & Name3"?

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24401 More actions · Answer 2

Assuming a low number of "Work" items, this pattern will work

SELECT
    d.[Date]
  , [Security]     = STUFF((SELECT ' & ' + w.[Name]
                            FROM #SampleData AS w
                            WHERE w.[Date] = d.[Date]
                              AND w.[Work] = 'Security'
                            FOR XML PATH(''), TYPE).value('text()[1]','VARCHAR(MAX)'
                           ), 1, 3, '')
  , [Driver]       = STUFF((SELECT ' & ' + w.[Name]
                            FROM #SampleData AS w
                            WHERE w.[Date] = d.[Date]
                              AND w.[Work] = 'Driver'
                            FOR XML PATH(''), TYPE).value('text()[1]','VARCHAR(MAX)'
                           ), 1, 3, '')
  , [Receptionist] = STUFF((SELECT ' & ' + w.[Name]
                            FROM #SampleData AS w
                            WHERE w.[Date] = d.[Date]
                              AND w.[Work] = 'Receptionist'
                            FOR XML PATH(''), TYPE).value('text()[1]','VARCHAR(MAX)'
                           ), 1, 3, '')
FROM #SampleData AS d
GROUP BY d.[Date]

How to post data/code on a forum to get the best help.