query updation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query updation

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-09-09 : 03:25:22

have one table with following columns

id name age address salary
1 12 abc $100

now i am using a query
Select

Case when id='' then 'id,' Else '' End +
Case when name= '' then 'name,' Else '' End +
Case when age= '' then 'age,' Else '' End +
case when address= ''then 'Sum_Insured' else '' End +

Case when salary= '' then 'salary,' Else '' End as nullcolumn from table

this query fetch all the column which are bank for a perticular id as nullcolumn

now i want my resultset to be displayed according to id's, like:

id nullcolumn
1 name
1 age
1 address
1 ..... and so on

means the above query result ie. nullcolumn values to be displayed rowwise with respect to the id.

scoo

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 06:21:22

[code]
declare @tbl as table(id int,nm varchar(10), age int, address char(10), salary int)
insert into @tbl
select 1,'abc', 12,'addr', 100 union all
select 2,'abc1', 13,'addr1', 200

select * from @tbl

declare @id varchar(10)

select * into #temp from
(
select convert(varchar(10),id)id, nm,convert(varchar(10),age)age,convert(varchar(10),salary)salary from @tbl
)u

unpivot (col for NullColumns in(id,nm,age,salary))v outer apply(select top 1
convert(varchar(10),id)id from @tbl t where convert(varchar(10),t.id)=v.col)T

update #temp set @id=id=case when id is null then @id else id end

select ID,NullColumns from #temp

drop table #temp

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources