drop table #Table1
GO
drop table #Table2
GO

create table #Table1(
TabID int
,GSP int
,GrpInfo varchar(100)
,GrpTypes varchar(50)
,GrpTypeId int
,IsAppPriv bit
)

Insert into #Table1
select 162, 1,   'A1G1', 'TEAM_A1', 1,  1 UNION ALL
select 162,	1,	 'A1G2', 'TEAM_A1',	2,	0 UNION ALL
select 162,	1,	 'A1G3', 'TEAM_A1',	3,	0 UNION ALL
select 134,	1,	 'A1G5', 'TEAM_A1',	1,	1 UNION ALL
select 134,	1,	 'A1G4', 'TEAM_A1',	2,	0 UNION ALL
select 147,	1,	 'A1G6', 'TEAM_A1',	1,	1 UNION ALL
select 164,	1,	 'A1G6', 'TEAM_A1',	1,	1 UNION ALL
select 147,	1,	 'B1G3', 'TEAM_B1',	2,	0 UNION ALL
select 164,	1,	 'B1G3', 'TEAM_B1',	2,	0 UNION ALL
select 147,	1,	 'A1G3', 'TEAM_A1',	3,	0 UNION ALL
select 164,	1,	 'A1G3', 'TEAM_A1',	3,	0 UNION ALL
select 134,	1,	 'A1G3', 'TEAM_A1',	3,	0 UNION ALL
select 147,	2,	 'B1G2', 'TEAM_B1',	4,	0 UNION ALL
select 164,	2,	 'B1G2', 'TEAM_B1',	4,	0 UNION ALL
select 147,	2,	 'B1G1', 'TEAM_B1',	1,	0 UNION ALL
select 164,	2,	 'B1G1', 'TEAM_B1',	1,	0 UNION ALL
select 147,	2,	 'B1G3', 'TEAM_B1',	1,	1 UNION ALL
select 164,	2,	 'B1G3', 'TEAM_B1',	1,	1 UNION ALL
select 134,	2,	 'B1G2', 'TEAM_B1',	1,	0 UNION ALL
select 162,	3,	 'C1G1', 'TEAM_C1',	1,	1 UNION ALL
select 162,	3,	 'C1G2', 'TEAM_C1',	2,	0 UNION ALL
select 162,	4,	 'D1G3', 'TEAM_D1',	3,	0 UNION ALL
select 134,	4,	 'D1G3', 'TEAM_D1',	3,	0 UNION ALL
select 147,	4,	 'D1G3', 'TEAM_D1',	3,	0 UNION ALL
select 164,	4,	 'D1G3', 'TEAM_D1',	3,	0 

SELECT TabID,GSP, 
           MAX( CASE seq WHEN 1 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 2 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 3 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 4 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 5 THEN GrpInfo ELSE '' END ) as 'ALL TEAMS'
           FROM ( SELECT t1.TabID, t1.GSP,t1.GrpInfo,
                    ( SELECT COUNT(*) 
                        FROM #Table1 t2
                       WHERE t2.TabID = t1.TabID
                         AND t2.GSP = t1.GSP
						 AND t2.GrpInfo <= t1.GrpInfo  )
               FROM #Table1 t1 ) D 
( TabID, GSP, GrpInfo,seq )
     GROUP BY TabID , GSP
	 ORDER BY TabID

from the above query, i need to extract data & store in the foll. table:

create table #Table2(
TabID int
,TEAM_A1 varchar(300)
,TEAM_B1 varchar(300)
,TEAM_C1 varchar(300)
,TEAM_D1 varchar(300)
,A1_SUB_1 varchar(300) --calculated using GSP=1 & GrpTypeId=1
,A1_SUB_2 varchar(300) --calculated using GSP=1 & GrpTypeId=2
,B1_SUB_1 varchar(300) --calculated using GSP=2 & GrpTypeId=1
)
expected Output

TabID   TEAM_A1            TEAM_B1          TEAM_C1    TEAM_D1 A1_SUB_1 A1_SUB_2 B1_SUB_1
134   A1G3, A1G4, A1G5,    B1G2,		 NULL	      D1G3,  A1G1	A1G2	B1G1
147   A1G3, A1G6, B1G3,   B1G1, B1G2, B1G3,  NULL	  D1G3,   A1G1	A1G2	B1G1     
162   A1G1, A1G2, A1G3,   NULL		   C1G1, C1G2,    D1G3,   A1G1	A1G2	B1G1    
164   A1G3, A1G6, B1G3,  B1G1, B1G2, B1G3,  NULL	  D1G3,   A1G1	A1G2	B1G1

insert into #Table2 (TABID,TEAM_A1)
SELECT   TABID,
           MAX( CASE seq WHEN 1 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 2 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 3 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 4 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 5 THEN GrpInfo ELSE '' END ) as 'ALL TEAMS'
           FROM ( SELECT t1.TabID, t1.GSP,t1.GrpInfo,
                    ( SELECT COUNT(*) 
                        FROM #Table1 t2
                       WHERE t2.TabID = t1.TabID
                         AND t2.GSP = t1.GSP
						 AND t2.GrpInfo <= t1.GrpInfo  )
               FROM #Table1 t1 ) D 
( TabID, GSP, GrpInfo,seq )
where GSP = 1
     GROUP BY TabID , GSP

update #Table2
set TEAM_B1 = 
(SELECT  
           MAX( CASE seq WHEN 1 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 2 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 3 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 4 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
           MAX( CASE seq WHEN 5 THEN GrpInfo ELSE '' END ) as 'ALL TEAMS'
           FROM ( SELECT t1.TabID, t1.GSP,t1.GrpInfo,
                    ( SELECT COUNT(*) 
                        FROM #Table1 t2
                       WHERE t2.TabID = t1.TabID
                         AND t2.GSP = t1.GSP
						 AND t2.GrpInfo <= t1.GrpInfo  )
               FROM #Table1 t1 ) D 
( TabID, GSP, GrpInfo,seq )
where GSP = 2 AND TABID = 134
     GROUP BY TabID , GSP)

SELECT	TabID,
	TEAM_A1	= MAX(CASE WHEN GSP = 1 THEN [ALL TEAMS] END),
	TEAM_B1	= MAX(CASE WHEN GSP = 2 THEN [ALL TEAMS] END),
	TEAM_C1	= MAX(CASE WHEN GSP = 3 THEN [ALL TEAMS] END),
	TEAM_D1	= MAX(CASE WHEN GSP = 4 THEN [ALL TEAMS] END)
FROM
(
	SELECT 	TabID,
		GSP, 
		MAX( CASE seq WHEN 1 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
		MAX( CASE seq WHEN 2 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
		MAX( CASE seq WHEN 3 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
		MAX( CASE seq WHEN 4 THEN GrpInfo + ',' ELSE '' END ) + ' ' +
		MAX( CASE seq WHEN 5 THEN GrpInfo ELSE '' END ) AS 'ALL TEAMS'
	FROM 	(	
			SELECT	t1.TabID, 
				t1.GSP,
				t1.GrpInfo,
	                    	( 
					SELECT 	COUNT(*) 
					FROM 	#Table1 t2 
					WHERE 	t2.TabID 	= t1.TabID 
					AND 	t2.GSP 		= t1.GSP 
					AND 	t2.GrpInfo 	<= t1.GrpInfo  
				)
			FROM 	#Table1 t1 
		) D ( TabID, GSP, GrpInfo, seq )
	GROUP BY TabID , GSP
) G
GROUP BY TabID
ORDER BY TabID

quote:

---

Originally posted by khtan

Don't quite understand how do you get the A1_SUB_1, A1_SUB_2 & B1_SUB_1.

---