• Home
  • Audio
  • Coding
  • Photo
  • About
  •        

     

    March 1st, 2012

    CREATE TABLE #team_members
    	(
    	id numeric(10) identity,
    	Name varchar(100),
    	Team varchar(100)
    	)
    
    INSERT INTO #team_members SELECT 'Vasili', 'R&D'
    INSERT INTO #team_members SELECT 'Peter1', 'HR'
    INSERT INTO #team_members SELECT 'Peter2', 'HR'
    INSERT INTO #team_members SELECT 'Maxim',  'R&D'
    INSERT INTO #team_members SELECT 'Peter3', 'HR'
    INSERT INTO #team_members SELECT 'David',  'Security'
    
    
    
    
    
    The table #team_members will look like this:
    
    id	Name	Team
    1	Vasili	R&D
    2	Peter1	HR
    3	Peter2	HR
    4	Maxim	R&D
    5	Peter3	HR
    6	David	Security
    
    
    
    
    Then we create #t table, with a blank field of 1000 chars named MembersList, 
    and fill it with ordered by our grouping field list of values from #team_members:
    
    SELECT Name, Team, replicate(' ',1000) as MembersList
    INTO #t
    FROM #team_members
    ORDER BY Team
    
    
    DECLARE @List varchar(1000)
    SET @List=''
    
    DECLARE @PrevTeam varchar(100)
    SET @PrevTeam=''
    
    UPDATE #t SET
    	@List = MembersList = (CASE WHEN Team=@PrevTeam then (@List + ',' + Name) ELSE Name END),
    	@PrevTeam = Team
    
    SELECT DISTINCT 
    	Team, 
    	(
    	SELECT TOP 1 q.MembersList 
    	FROM #t as q 
    	WHERE q.Team=#t.Team 
    	ORDER BY LEN(q.MembersList) DESC
    	) AS Members 
    FROM #t
    
    
    In the end we get:
    
    #t:
    
    Name	Team	MembersList
    Peter1	HR	Peter1
    Peter2	HR	Peter1,Peter2
    Peter3	HR	Peter1,Peter2,Peter3
    Maxim	R&D	Maxim
    Vasili	R&D	Maxim,Vasili
    David	Security	David
    
    And the last select:
    Team	Members
    HR	Peter1,Peter2,Peter3
    R&D	Maxim,Vasili
    Security	David
    






    By s3t © 2011