IR Punch IR Punch - 4 months ago 10
SQL Question

Shrink and Group Column using Pivot in SQL Server

I'm having a SQL Table:

DECLARE @team TABLE (Department VARCHAR(20),TeamMember1 VARCHAR(50),TeamMember2 VARCHAR(50),TeamMember3 VARCHAR(50))

INSERT INTO @team VALUES('CRM','Tom', 'Harry', 'Rodger')
INSERT INTO @team VALUES('ERP','Boby', 'Simha', 'Wonder')
INSERT INTO @team VALUES('HR','Jack', 'John', 'Jim')

SELECT t.* FROM @team t


The Output of the Table should be

enter image description here

My Expected Output Should be

enter image description here

I referred many tutorial but I can't able to find a solution. So, kindly assist me... This a temporary table, I need to implement the same logic in my main project.

Answer
SELECT t.*
FROM @team
CROSS APPLY (
    VALUES ('TeamMember1', TeamMember1)
         , ('TeamMember2', TeamMember2)
         , ('TeamMember3', TeamMember3)
) t(TeamName, TeamMember)
Comments