Grouping data column wise

• sudhanva

  Ten Centuries

  Points: 1343

  More actions

  May 22, 2009 at 11:38 am

  #131528

  Hi,

  I have a data like this:

  NameGeoNameID

  -----------------------------------

  JOHNTerritory7

  JOHNREGION7

  JOHNAREA7

  SMITHTerritory9

  SMITHREGION9

  SMITHAREA9

  I need to show it like this:

  NameGeoNameID

  -----------------------------------

  JOHNTerritory7

  REGION7

  AREA7

  SMITHTerritory9

  REGION9

  AREA9

  How can i achieve this?

  Help me.

  Query For your convenience:

  create table #temp

  (

  [Name] varchar(10),[Geo] varchar(10), [NameID] int

  )

  insert into #temp values('JOHN','Territory',7);

  insert into #temp values('JOHN','REGION',7);

  insert into #temp values('JOHN','AREA',7);

  insert into #temp values('SMITH','Territory',9);

  insert into #temp values('SMITH','REGION',9);

  insert into #temp values('SMITH','AREA',9);

  Thanks & Regards,

  Sudhanva

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 22, 2009 at 11:44 am

  #999054

  You can use a CTE and ROW_NUMBER over NameId PARTITION:

  DECLARE @t TABLE (Name VARCHAR(20), Geo VARCHAR(20), NameId INT)

  INSERT INTO @T

  SELECT 'JOHN', 'Territory', '7'

  UNION ALL SELECT 'JOHN', 'REGION', '7'

  UNION ALL SELECT 'JOHN', 'AREA', '7'

  UNION ALL SELECT 'SMITH', 'Territory', '9'

  UNION ALL SELECT 'SMITH', 'REGION', '9'

  UNION ALL SELECT 'SMITH', 'AREA', '9'

  ; WITH t (Name, Geo, NameId, RowNum) AS

  (

  SELECT

  Name,

  Geo,

  NameId,

  ROW_NUMBER() OVER (PARTITION BY NameId ORDER BY NameId)

  FROM @t

  )

  SELECT

  CASE WHEN RowNum = 1 THEN Name END,

  Geo,

  NameId

  FROM t

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 22, 2009 at 11:49 am

  #999059

  Thanks for the test data. Just a little bit too late 😀