Transpose rows as columns

[User 4005525]

Hi I have a table with columns like Table1: 1.ID as int 2.TableID as int 3.ColumnName Varchar(50) 4.ColumnValue Varchar(50) Here TableID is not unique.I want to create a new table. Each ColumnName value of Table1 should become Column of New Table. Each ColumnValue should become the row value of corresponding column. Table1 values: ID TableID ColumnNAme ColumnValue 1 1 FirstNAme Javio 2 1 LastName Choprakhush 3 1 DateOfBirth 13/09/1986 4 1 Phone 6222226779 And i want to create a table like this FirstName LastNAme DateOfBirth Phone JAvio Choprashush 13/09/1986 6222226779 Can anyone Help ME? Thanks in advance sri

[Ashfield]

This sould put you on the right track

/* create test table and populate */
create table UserArea(Country varchar(20))
insert into UserAreaselect 'India'
union all
select 'USA'
union all
select 'India'
union all
select 'UK'
/* now the actual code */
DECLARE @SQL nvarchar(4000)
SET @SQL=''
SELECT @SQL= @SQL +'SUM(CASE WHEN Country=''' + a.Country + ''' THEN 1 ELSE 0 END) AS [' + a.Country + '],'
FROM (select distinct Country from UserArea) as a
select @SQL = left(@SQL,len(@SQL)-1)
SET @SQL='SELECT ' + @SQL + ' FROM UserArea'EXEC(@SQL)
/* tidy up */
drop table UserArea

Bob Ashfield Consultants Ltd

[A Wong]

You can try using PIVOT if this is SQL2005.