Problem in Query

garga1

Hi Friends, I hv got 2 fields in a table. Year and title. year Title 1945 t1 1945 t2 1945 t3 i want a query to retrieve answer like foll. : 1945 //Field name t1 t2 t3 1945 should be a field name and under that i need 3 rows where i can display title values.. Thanks in advance

Marek Grzenkowicz

Why exactly do you need something like this? It looks weird. You want do mix pieces of different information in the same column.

Anurag Gandhi

Use Union select distinct Year as [ColumnName] from myTable union select Title from myTable Use group by in first query if required in your circumstance.

Anurag Gandhi. http://www.gandhisoft.com Life is a computer program and every one is the programmer of his own life.

Ashfield

What you are trying to achieve (I think) is known as pivoting. This sample should point you in the right direction.

/* create test table and populate */

create table UserArea(Country varchar(20))

insert into UserArea
select '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

garga1

Thanks yaar. It works.. But can we get a output like this : 1945 1950 1960 t1 t4 t6 t2 t5 t7 t3 i hv also added group by. But it doesn't work..

Niraj_Silver

hi Niraj Here , I hope this will work for you.... try this.. Select fieldnamewhich containst1t2ec as [1945] From Tablename Where year = 1945 Have a noce Day.. Stay Tune ..... Take Care.... :-D